String padding in tsql

Question

I print out a bunch of DDL statements that are dynamically created and want to align the output in a specific way.

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)

What 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 pad the string by n of character x. I would use it like this:

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

Thanks

Justin Swartsel · Accepted Answer · 2009-12-11 18:37:32Z

4

I believe what you want is the REPLICATE function available from SQL 2005 on.

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

MSDN: REPLICATE

answered Dec 11, 2009 at 18:37

Justin Swartsel

3,4311 gold badge23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Maximilian Mayerl · Accepted Answer · 2009-12-11 18:37:38Z

1

You can use the SPACE function:

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

If it's something else than a space you want to insert, you can use REPLICATE(varchar, int).

answered Dec 11, 2009 at 18:37

Maximilian Mayerl

11.4k3 gold badges37 silver badges42 bronze badges

Comments

OMG Ponies · Accepted Answer · 2009-12-11 18:39:35Z

1

You need to use the REPLICATE function, along with DATALENGTH:

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

answered Dec 11, 2009 at 18:39

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Collectives™ on Stack Overflow

String padding in tsql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related