Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
Hi All,I have a string where the size can vary so i haveWE 12I 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 charactersand also if it isWERthen 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)) ENDFROM @t