Data verification question

Question

Post reply

Data verification question

SQLUserC

SSCrazy

Points: 2345
More actions
April 12, 2010 at 11:50 am

#217739

Hey all,
I am converting data from text-based reports, and reading the data into a SQL database. I am in charge of checking to ensure that the data was read-in correctly. One thing I want to check for is non-numeric characters (i.e. letters, punctuation) in a field that is a string, but should be filled with only numbers (e.g. zip code).
Does anyone know a concise way to bring up values that contain non-numeric characters? The only way that I know how to involves writing a long select query using 'like' conditions to specify the different characters I want to check for. I was hoping that maybe I could use some sort of value keyword to attempt to convert the zip codes to integers, and that it would let me know of any zip codes that contain non-numbers. I would appreciate any advice that anyone can give.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

this is probably the easiest way: using the LIKE statement and the pseudo regular expression:

select zipcode,* from YourTable where zipcode like '%[^0-9]%'

that will find anything with characters outside of the 0-9 range;

in my case, i found a zipcode with the value "74346+"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 2

SELECT cols

FROM YourTable

where YourColumn like '%[0-9]%'

will return only rows with numbers.

SELECT cols

FROM YourTable

where YourColumn like '%[^0-9]%'

will return only rows at least one non-numeric ~~value~~ edit: character.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

lmu92 (4/12/2010)
SELECT cols FROM YourTable
where YourColumn like '%[0-9]%'
will return only rows with numbers.
SELECT cols
FROM YourTable
where YourColumn like '%[^0-9]%'
will return only rows at least one non-numeric ~~value~~ edit: character.

Careful... the first one will return [font="Arial Black"]ANY [/font]row containing even just one digit regardless of what the other characters are. The second one is correct.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLUserC SSCrazy Points: 2345 More actions · Answer 4

SQLUserC

SSCrazy

Points: 2345

April 12, 2010 at 2:19 pm

#1150341

That expression looks handy. Thanks!

SQLUserC SSCrazy Points: 2345 More actions · Answer 5

One more question on the topic. I've been trying to do some reading up on regular expressions, but most of the resources I get seem to be concerned with implementing them and are quite involved.

Is there a list of expressions like "[^0-9]" that it might be worthwhile to take a look at? I'd be interested in familiarizing myself with other shorthands expressions.

Thanks.

SQL Stud SSC Eights! Points: 987 More actions · Answer 6

SQL Stud

SSC Eights!

Points: 987

April 12, 2010 at 5:49 pm

#1150444

Very handy... thanks

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

cjohn5552 (4/12/2010)
One more question on the topic. I've been trying to do some reading up on regular expressions, but most of the resources I get seem to be concerned with implementing them and are quite involved.
Is there a list of expressions like "[^0-9]" that it might be worthwhile to take a look at? I'd be interested in familiarizing myself with other shorthands expressions.
Thanks.

I'm sure there is... I just don't know where because I've not needed full blown Regex in T-SQL yet.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLUserC SSCrazy Points: 2345 More actions · Answer 8

Hey,

I think I found a useful resource for the info I was looking for. It's in Books Online. The document is called "How to: Search with Regular Expressions". Hope it helps.