Tsql string padding

Question

I am printing a bunch of DDL statements that are dynamically generated and want to output the output appropriately.

PRINT 'ALTER TABLE ' + @TableName + ' WITH NOCHECK ADD CONSTRAINT CK_' + @TableName + '_' + @ColumnName + '_MinimumLength CHECK (LEN(' + @ColumnName + ') > 0)'

Output:

ALTER TABLE SignType ADD CONSTRAINT CK_SignType_Description_MinimumLength CHECK (LEN(Description) > 0)
ALTER TABLE Person ADD CONSTRAINT CK_Person_Name_MinimumLength CHECK (LEN(Name) > 0)

I want the output to be:

ALTER TABLE SignType                WITH NOCHECK ADD CONSTRAINT CK_SignType_Description_MinimumLength                CHECK (LEN(Description) > 0)
ALTER TABLE Person                  WITH NOCHECK ADD CONSTRAINT CK_Person_Name_MinimumLength                         CHECK (LEN(Name) > 0)

Is there a function that allows me to stuff a string into n characters x. I would use it as follows:

PRINT 'ALTER TABLE ' + @TableName + PAD(' ', 50 - LEN(@TableName)) + ' WITH NOCHECK ADD CONSTRAINT .....'

thanks

Answer 1

I believe you need a feature REPLICATEavailable since SQL 2005. on.

PRINT 'ALTER TABLE ' + @TableName + REPLICATE(' ', 50 - LEN(@TableName)) + ' WITH NOCHECK ADD CONSTRAINT .....'

MSDN: REPLICATE

Answer 2

You can use the SPACE function:

PRINT 'ALTER TABLE ' + @TableName + SPACE( 50 - LEN(@TableName)) + ' WITH NOCHECK ADD CONSTRAINT .....'

If this is something other than the space you want to insert, you can use REPLICATE (varchar, int).

Answer 3

REPLICATE, DATALENGTH:

PRINT 'ALTER TABLE ' + @TableName + REPLICATE(' ', 50 - DATALENGTH(@tablename)) + ' WITH NOCHECK ADD CONSTRAINT .....'