Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ugh3012
Yak Posting Veteran
62 Posts |
 Posted - 2013-12-06 : 14:22:08
|
I have like 100 columns and most of them work fine, but some will not work. I need to select the columns with correct width for fixed width flat file. Here is typical SQL statment that works for most of them. left(RTRIM(A.City) + Replicate(' ', 25) ,25) as [City]The above one is not working, but many other works fine like the address. Why would it work for some, but not others?It works fine if I use any char, but not space. i.e. left(RTRIM(A.City) + Replicate('*', 25) ,25) as [City] |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-12-06 : 14:24:32
|
For those that don't work, see what the data is - that is the only way to know. When there are trailing/leading spaces causing an issue, what I usually do is append a character to the beginning and end, so I can get a clear view of the spaces - for example, like this:'|' + left(RTRIM(A.City) + Replicate(' ', 25) ,25) +'|' as [City] |
 |
|
|
ugh3012
Yak Posting Veteran
62 Posts |
Posted - 2013-12-06 : 14:32:25
|
| I added that pipe and I see the spaces; however, when I wrap that line with LEN() it does not tell me the correct number. i.e.Len(left(RTRIM(A.City) + Replicate('*', 25) ,25)) as [City]it returns the number of char for city, but I am expecting 25. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-12-06 : 15:01:30
|
SQL ignores trailing spaces when calculating length. If you want to include trailing spaces in the calculation, use DATALENGTH function. You have to careful using DATALENGTH though, because the value it returns will be different depending on whether you are using varchar or nvarchar. Another alternative, which does not have that problem, is this:Len(left(RTRIM(A.City) + Replicate(' ', 25) ,25) + '|') - 1 as [City] |
|
|
|
ugh3012
Yak Posting Veteran
62 Posts |
Posted - 2013-12-06 : 15:36:16
|
| Interesting. Thanks for the help. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-12-10 : 00:46:59
|
| In place of replicate(' ',25) you can also use space(25)MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Padding spaces