Pad spaces to the end of a string

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pad spaces to the end of a string

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2011-06-29 : 05:36:20

Why wont the below code add spaces to the end of my field?
It will add the 3 to the front but not the 4 at the end?


SELECT  
CASE
WHEN ISNULL(PreviousBand,'') = '' THEN ''
WHEN LEN(PreviousBand) = 8  THEN ed.PreviousBand
WHEN RTRIM(LTRIM(SUBSTRING(PreviousBand,1,1))) IN ('2','3') THEN  RTRIM(LTRIM(PreviousBand)) + space(4)
ELSE space(3) + RTRIM(LTRIM(ed.PreviousBand)) 
END 
FROM dbo.EmpDetails ed

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-29 : 05:46:52

see this example:

set ansi_padding on
create table sample (test varchar(255))
insert sample select 'Fred'
select '<'+test+'>' from sample
update sample set test = test + space(4)
select '<'+test+'>' from sample

drop table sample

set ansi_padding off
create table sample (test varchar(255))
insert sample select 'Fred'
select '<'+test+'>' from sample
update sample set test = test + space(4)
select '<'+test+'>' from sample

drop table sample

and read here: http://msdn.microsoft.com/en-us/library/ms187403%28v%3Dsql.90%29.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

Looper
Yak Posting Veteran

68 Posts

Posted - 2011-06-29 : 06:04:43

Still can't get the spaces to append. The field is a varchar(8) that allows nulls - I can't amend this data type. I am selecting the data out by a select statement for importing into a csv file, therefore I need to manipulate the data to pad the spaces in the select statement.

sshelper
Posting Yak Master

216 Posts

Posted - 2011-07-02 : 02:35:12

Try CASTing the output of the CASE statement to CHAR(8). This will change the data type of the output without affecting the data type of the table and it will give you the spaces you need at the end of the string.

Regards,
SQL Server Helper
http://www.sql-server-helper.com/es/mensajes-de-error/mensaje-1-500.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources