0

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?

Hituptony
  • 2,740
  • 3
  • 22
  • 44
  • http://stackoverflow.com/questions/12501038/right-pad-a-string-with-variable-number-of-spaces - This would help – TMNT2014 Jun 18 '14 at 19:15
  • will padding work if there is nothing to pad? – Hituptony Jun 18 '14 at 19:17
  • Oh, dear. I hope this is a homework assignment. If you are being asked to do this to a table in a production database, you may want to polish up your resume and see if you find some gainful employment. – TommCatt Jun 20 '14 at 00:01
  • @TommCatt Oh, Santa...this is one part of a specific layout in one project. The table is somewhat irrelevant in that the flat file destination of the file needs to be a certain width. Nobody asked me to do it, but it needs to be done. That's why I'm here. I think my resume is fine. Check it out www.linkedin.com/pub/anthony-borgetti/43/34/6b9/ – Hituptony Jun 20 '14 at 12:47
  • If a table is truly irrelevant, it shouldn't be taking up space in a database. If you need the table to perform an operation, it is not irrelevant. I think what you mean is that the form of the data in the table is irrelevant. But data should always be kept in the most primitive, "natural" form as possible. If it needs to be formatted a certain way (and what data doesn't), then perform the formatting as you extract the data for that specific use. If the table is useful /here/, than it may not be too long before you or someone else finds it also useful /there/. This is raw data, keep it clean. – TommCatt Jun 20 '14 at 16:22
  • I've decided to use SSIS to build this data anyway. This was my test and the requirements for such a layout. I have more of a MySQL background so I had no idea how to get such a file. Thanks for your input – Hituptony Jun 20 '14 at 18:36

2 Answers2

1

You're going to want to use the replicate function. 

SELECT REPLICATE(' ',256)

This function will repeat space (or whatever string you put in the first parameter) 256 (or however many in the second parameter) times.

Elias
  • 2,602
  • 5
  • 28
  • 57
0

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.

mdisibio
  • 3,148
  • 31
  • 47