How to select records with LETTERS in an nvarchar field that should only be numbers?

Question

I have a table of about 90K records. One of the fields is nvarchar(20) and should only contain NUMBERS. However some contain an alpha mix.

Can someone suggest a quick way to identify the records that have LETTERS imbedded in the string?

Thanks, John

Answer 1

0

Use PATINDEX to search for letters

select * from mytable where patindex('[a-z]', mycolumn) > 0

assuming it is case-insenstive, otherwise

select * from mytable where patindex('[a-z]', lower(mycolumn) ) > 0

If the requirement is twisted somewhat to say ONLY numbers are allowed, then

select * from mytable where mycolumn like '%[^0-9]%'

As mentioned in the comment to another answer, ISNUMERIC will return true if the string can be converted to a numeric data type (which includes float, real and money). Because of this, some non-number characters are acceptable '+', '-', 'd', 'e', and currency symbols e.g. '$', '£'

answered Oct 21 '09 at 09:19 AM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

The last query will select all rows that contain only numbers - because you're saying NOT like and also using the ^ excluder in the pattern. Also, i'd be interested to see whether patindex('[a-zA-Z]', myColumn) might be more efficient than lower(myColumn)?

Oct 21 '09 at 11:53 AM Matt Whitfield ♦♦

oops! cheers for spotting that Matt, fixed now!

Oct 21 '09 at 12:27 PM Kev Riley ♦♦

(comments are locked)

Answer 2

0

Select *
from Table
where ISNUMERIC(Field) = 0

This will probably use a table scan because of the function on field, but with only 90k records, it shouldn't be a problem

answered Oct 21 '09 at 08:50 AM

Paul Ward
99

Be careful with ISNUMERIC. Try select isnumeric('111d1') it will return '1' i.e.true. This is because it is testing to see whether the string can be converted to a numeric data type.

Oct 21 '09 at 09:15 AM Kev Riley ♦♦

(comments are locked)

Answer 3

0

Since only letters need to be identified:

-- covers case Sensitive, if necessary
select * from @sample
where data like '%[a-z]%' or data like '%[A-Z]%'

answered Oct 21 '09 at 11:35 AM

Bob Hovious
1.6k ● 5 ● 6 ● 9

(comments are locked)

How to select records with LETTERS in an nvarchar field that should only be numbers?

Follow this question

Related Questions