Help a newbie....please???
-
I need to update all the zipcodes in one of the tables in my database. Right now some are 5 digit zipcodes and the others are 5 + 4 zipcodes. I need to update it so that it stips off the +4 zipcodes and just leaves me the 5 digits. I am not sure what I need to do to accomplish this. I am very new to sql and any help would be appreciated.
Thankx
-
To build the exact query I may need more info
But here is how you do it :
Update TableName SET FldZipcode = CASE WHEN Len(LTRIM(RTRIM(FldZipCode))) = 5 THEN FldZipCode ELSE Left(LTRIM(FldZipCode),5) END
If you need to make sure before you update the table Run this:
SELECT FldZipCode as InitialValue , CASE WHEN Len(LTRIM(RTRIM(FldZipCode))) = 5 THEN FldZipCode ELSE Left(LTRIM(FldZipCode),5) END AS NewValue
From TableName
* Noel -
see a sample
Col1 -- is the unique column
col2 is the zip code column..
update table2 set col2 = a.col2 from table2 join (select col1,substring(col2,1,5) as col2 from table2 )a on table2.col1=a.col1
Thanks
Linto
-
Worked great, thanks for your help 400 Posts!......
-
Just call him noeld--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Right
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply