Search code examples
sqldatabasesql-server-2012fillreplicate

Quick way to space fill column 256 chars SQL-Server 2012

So i have a file I'm creating using SQL Server 2012.

Many of the columns are optional or unused, and in place of the characters that would normally be there we are asked to zero-fill numeric columns, and space-fill alphanumeric columns.

Now I have a column called CDD and it's 256 characters long.

Is there a simpler way I can fill this column other than pressing the space bar 256 times in single quotes?

The file is Fixed Width so I have to have 256 spaces in this column for it to import correctly. I was looking at replicate and stuff, but they don't make sense being that the column doesn't have an original string to replace.

Replicate works with zeros but how can I validate it with spaces? The column doesn't expand like it would if there was an actual character in it...Does SQL-Server do any collapsing of white space in this way?

Solution

  • In addition to REPLICATE you can also use 

    SELECT SPACE(256);

    As far as "the column expanding", the column will not appear expanded in SSMS unless you click on 'Results in Text' (instead of grid). If you use the LEN function it will return 0, but DATALENGTH will return either the actual number of spaces requested for a varchar column, or the defined length of a char column. Either way, if you copy the output into a text editor, you will see that it is indeed a string of empty spaces.