| Author |
Topic |
|
coleman8er
Starting Member
7 Posts |
 Posted - 2008-08-14 : 14:02:42
|
| I have a column in a table that has four zeros in front of every record (i.e. 00001234).How do I use the TRIM function to cut all of the zeros off of this column? I have an incomplete script pasted below, I hope someone can help me out here!UPDATE CustomerSET ID = LTRIM(ID) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-14 : 14:07:16
|
| UPDATE CustomerSET ID=CAST(ID AS int) |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-14 : 14:07:48
|
| UPDATE CustomerSET ID=REPLACE(ID,'0','') |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-14 : 14:08:39
|
Visakh16, what will happen to the value '00001201230' ?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-14 : 14:09:14
|
SELECT REPLACE(LTRIM(REPLACE(Col1, '0', ' ')), ' ', '0')FROM Table1
E 12°55'05.25"N 56°04'39.16" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-14 : 14:13:32
|
quote:
Originally posted by Peso
Visakh16, what will happen to the value '00001201230' ?
E 12°55'05.25"N 56°04'39.16"
yup it wont work |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-08-14 : 15:49:52
|
quote:
Originally posted by visakh16
UPDATE CustomerSET ID=CAST(ID AS int)
I'm curious why this doesn't work. I noticed that visakh16 pasted a different solution and wondered why this one wasn't sufficient. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-14 : 15:54:04
|
It will work, as long as there are only number (no characters).It is also implied that the length of the text should be less than 10 digits (110 if it starts with a 1).
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-08-14 : 15:56:07
|
| I gotcha. In my mind those conditions were a given, but I see where it would fail. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-15 : 13:11:58
|
| Peso, your code will fail if there are embedded spaces, or if there are more than four leading zeros in the ID.Try '000001201230' and '00001201230', which both yield the same result.Or this: '000012 01230', which truncates more than the leading four zeros.According to the poster's specifications, all he needs is this:select stuff(ID, 1, 4, '')Boycott Beijing Olympics 2008 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-15 : 13:46:18
|
Yes. You are right of course.Until OP reports back with a clear and present spec, this will doselect substring(col1, patindex('%[^0]%', Col1), 120)from table1orselect substring(col1, patindex('%[1-9]%', Col1), 120)from table1
E 12°55'05.25"N 56°04'39.16" |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-15 : 13:56:48
|
| Those will do wrong.Both of those still fail when there are more then four leading zeros. Or less than four leading zeros, for that matter.This appears to me to be the most robust solution:select coalesce(stuff(col1, patindex('0000%', col1), 4, ''), col1)Boycott Beijing Olympics 2008 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-15 : 15:20:50
|
Yes, you are right.I read the original post more thorough now and it seems that there are ALWAYS four leading zeros, and OP want them removed.Your STUFF(Col1, 1, 4, '') will do the job perfectly.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
TRIM function