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.
Author |
Topic |
emyk
Yak Posting Veteran
57 Posts |
Posted - 2013-06-20 : 13:42:53
|
I need to do a select of a column and if the length of the selected column not equals to 4 then update the column (left Pad with zero's). Expected column length's are 1,2,3,4. UPDATE table1SET col1 = (CASE WHEN Len(col1) = 3 THEN '0' + col1 WHEN Len(col1) = 2 THEN '00' + col1 WHEN Len(col1) = 1 THEN '000' + col1ELSE col1 END)and col1 IN (select col1 from table1 where LEN(col1) <> 4 ) Sample Data:Col10100234300210Desire output:Col100100002000304300210 |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-06-20 : 13:54:46
|
SELECT Right('0000'+Col1,4) FROM Table1 WHERE Len(Col1)<>4;If the result of the above query is the desired one then UPDATE Table1 SET Col1=Right('0000'+col1,4) WHERE Len(Col1)<>4CheersMIK |
|
|
shan007
Starting Member
17 Posts |
Posted - 2013-06-20 : 16:37:43
|
your request can be simplified without case statement as in below. Let me know if it helps.Assume col1 is varchar type select REPLICATE(0,4-LEN(col1)) + col1 from table1 where Len(col1)<>4==============================I'm here to learn new things everyday.. |
|
|
|
|
|