| Author |
Topic |
|
Maverick_
Posting Yak Master
107 Posts |
 Posted - 2010-06-21 : 05:11:46
|
| Hi allI am trying to write a SQL code that updates all street codes within my database and remove leading zeros where appropriate.The table that holds this is called central_street where Street_code is set to data type Character.Anyone know how I could do this? I am quite new to SQL so am not sure whether I should use Update statement or a standard SELECT.Kind regardsJ |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-06-21 : 05:14:37
|
| Are all street codes numeric? In that case:UPDATE central_street SET Street_code = CAST(Street_code AS int)- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-21 : 08:08:06
|
| orUPDATE central_street SET Street_code = Street_code*1MadhivananFailing to plan is Planning to fail |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-21 : 08:11:07
|
quote:
Originally posted by madhivanan
orUPDATE central_street SET Street_code = Street_code*1MadhivananFailing to plan is Planning to fail
or Street_code + 0
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-21 : 08:28:42
|
If not all values are numeric then try this:update central_streetset street_code = stuff(street_code,1,patindex('%[^0]%',street_code)-1,'')where left(street_code,1)='0'
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-21 : 09:35:06
|
quote:
Originally posted by Lumbago
Are all street codes numeric? In that case:UPDATE central_street SET Street_code = CAST(Street_code AS int)- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com
If the above query is modified this way it will take care of the street codes that are not numeric.UPDATE central_street SET Street_code = CAST(Street_code AS int) where IsNumeric(Street_code)=1Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-21 : 09:38:09
|
IsNumeric() isn't reliable for this.See for example: select convert(int,'1.0')
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-21 : 09:49:30
|
quote:
Originally posted by webfred
IsNumeric() isn't reliable for this.See for example: select convert(int,'1.0')
No, you're never too old to Yak'n'Roll if you're too young to die.
Well what if I do something like this.update central_street SET Street_code =convert(int,convert(decimal(18,2),Street_code)) where ISNUMERIC(name)=1  Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
|
Remove leading zeros from street codes