Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Determine if String has numbers in datatype varchar(10) Expand / Collapse
Author
Message
Posted Monday, March 9, 2009 8:21 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 2, 2014 1:47 PM
Points: 97, Visits: 363
I am trying to clean up our Zip Code table. Most of the time the zip code entry is Zip+4. I have to clean up so that I am only keeping first 5 of the zip. Problem is that many times zip code column also has letter for the applicants outside of the country, so I can't modify them. I am only allowed to clean up zip+4. The logic I am using right now is

update DimStudent
set StudentZip = substring(StudentZip, 1, 5)
where len(StudentZip) = 10 and substring(StudentZip, 6, 1) = '-'

Problem with this code is that StudentZip doesn't always have Zip+4 (10 characters) and sometimes they don't have a hyphen. Sometimes it has Zip+3 or Zip+2 (I know, weird).

So I am told that the logic I should be using is: Check if the first first 5 characters are numbers. If they are numbers, then keep just first 5 charaters (or delete everything after 5 characters).

I checked many website and two books on T-SQL but couldn't find a code that will check to see if a string is number. Please help.

Thanks in Advance.


------------
:)
Post #671540
Posted Monday, March 9, 2009 8:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
Try

update DimStudent
set StudentZip = substring(StudentZip, 1, 5)
where StudentZip like '[0-9][0-9][0-9][0-9][0-9]%'




Madhivanan

Failing to plan is Planning to fail
Post #671550
Posted Monday, March 9, 2009 8:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
Where MyZipColumn like '[0-9][0-9][0-9][0-9][0-9]%'

Will find anything that is five numbers, followed by anything or nothing.

Where MyZipColumn like '[0-9][0-9][0-9][0-9][0-9]'
or MyZipColumn like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
or MyZipColumn like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Will find anything that's five numbers and nothing else, or anything that's five numbers followed by a hyphen and then four numbers, or anything that's nine numbers (five and four) without a hyphen.

The braces with a range in them select anything that matches that range. In this case, any single digit that's between 0 and 9.

Does that help?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #671551
Posted Monday, March 9, 2009 8:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,212, Visits: 3,232
It goes LIKE this:

SELECT 
StudentZip,
SUBSTRING(StudentZip, 1, 5) AS CleanedZip
FROM DimStudents
WHERE (StudentZip LIKE '[0-9][0-9][0-9][0-9][0-9]%')

Post #671553
Posted Monday, March 9, 2009 8:37 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 2, 2014 1:47 PM
Points: 97, Visits: 363
Thanks a lot guys. It worked.

------------
:)
Post #671565
Posted Tuesday, March 10, 2009 1:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
Rav (3/9/2009)
Thanks a lot guys. It worked.

You are welcome :)




Madhivanan

Failing to plan is Planning to fail
Post #672201
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse