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
 General SQL Server Forums
 New to SQL Server Programming
 appending spaces in middle

Author  Topic 

anjali5
Posting Yak Master

121 Posts

Posted - 2012-03-12 : 14:28:23
Hi All,

I have a string where the size can vary so i have

WE 12

I want to make it 8 characters and I don't want to pad the spaces at the end., i want to pad the spaces in the middle to make it 8 characters.

so in the above e.g.

I want something like this

WE    12

another e.g is

WER  568

I want the output to be

WER  568

I want to make it 8 characters

and also if it is

WER

then I want

WER and padded with 5 spaces at the end.

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-12 : 14:46:23
Proof of concept:
DECLARE @t TABLE(t VARCHAR(12))
INSERT @t VALUES('WE 12')
INSERT @t VALUES('WER 568')
INSERT @t VALUES('WER')

SELECT CASE WHEN t LIKE '% %' THEN
LEFT(t,CHARINDEX(' ',t)-1) + SPACE(8-LEN(REPLACE(t,' ',''))) +
REVERSE(LEFT(REVERSE(t),CHARINDEX(' ',REVERSE(t))-1))
ELSE CAST(t AS CHAR(8)) END
FROM @t
Go to Top of Page

anjali5
Posting Yak Master

121 Posts

Posted - 2012-03-12 : 15:36:34
Thank you Sir!!
Go to Top of Page
   

- Advertisement -