Determine if String has numbers in datatype varchar(10)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Determine if String has numbers in datatype...

Determine if String has numbers in datatype varchar(10)

Rate Topic

Display Mode

Topic Options

Author

Message

//Ravi

Posted Monday, March 09, 2009 8:21 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 77, Visits: 257

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

Madhivanan-208264

Posted Monday, March 09, 2009 8:27 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 5:17 AM
Points: 329, Visits: 461

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

GSquared

Posted Monday, March 09, 2009 8:27 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

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

andrewd.smith

Posted Monday, March 09, 2009 8:28 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, 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

//Ravi

Posted Monday, March 09, 2009 8:37 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 77, Visits: 257

Thanks a lot guys. It worked.

------------
:)

Post #671565

Madhivanan-208264

Posted Tuesday, March 10, 2009 1:32 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 5:17 AM
Points: 329, Visits: 461

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 »

Permissions