SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Padding spaces
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ugh3012
Yak Posting Veteran

62 Posts

Posted - 12/06/2013 :  14:22:08  Show Profile  Reply with Quote
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]


Edited by - ugh3012 on 12/06/2013 14:24:17

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 12/06/2013 :  14:24:32  Show Profile  Reply with Quote
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]
Go to Top of Page

ugh3012
Yak Posting Veteran

62 Posts

Posted - 12/06/2013 :  14:32:25  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 12/06/2013 :  15:01:30  Show Profile  Reply with Quote
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]
Go to Top of Page

ugh3012
Yak Posting Veteran

62 Posts

Posted - 12/06/2013 :  15:36:16  Show Profile  Reply with Quote
Interesting. Thanks for the help.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 12/10/2013 :  00:46:59  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
In place of replicate(' ',25) you can also use space(25)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000