fixing a string - SQL Server Forums

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)

fixing a string

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2011-08-18 : 18:05:50

i have a tables with string of length 5 like :
00005
00256
00022
00010
00122
i want to cahange the above to a structure of
ABCD05
ABCD256
ABCD22
ABCD10
ABCD122
which means :
minimum 6 letters,starts with ABCD, and all left zero's should be removed, whats the best way to do it?
remark : in case of 00005, i add a zero on the left and then i get the minimu, of 6 including the ABCD

thanks:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-18 : 18:10:52

'ABCD' + right('00' + convert(varchar(10), convert(int, col)), 2)

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 02:11:55

quote:
Originally posted by khtan

'ABCD' + right('00' + convert(varchar(10), convert(int, col)), 2)

KH
[spoiler]Time is always against us[/spoiler]

wont work for 00256 & 00122

as they will become
ABCD56,ABCD22 etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 02:30:18

i think it should be


SELECT 'ABCD' + COALESCE(REPLICATE('0',2-LEN(convert(varchar(10),convert(int, col)))),'') + convert(varchar(10),convert(int, col))
FROM Table

this will return correct result irrespective of number non-zero digits present in string and would ensure minimum of six characters returned in string result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-19 : 02:35:58

you are right.

this should do it


'ABCD' + case 	when convert(int, col) < 10
			then right(col, 2)
			else convert(varchar(10), convert(int, col))
			end

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources