x

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

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

more ▼

asked Oct 21, 2009 at 08:18 AM in Default

avatar image

JMasciantoni
11 1 1 3

Excellent answers all and thanks for the very quick response.

Oct 21, 2009 at 12:19 PM JMasciantoni

John, have you thought about accepting one of the answers as your 'Accepted Answer'? It will help others in future.

Oct 26, 2009 at 08:27 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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. '$', '£'

more ▼

answered Oct 21, 2009 at 09:19 AM

avatar image

Kev Riley ♦♦
64k 48 61 81

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 21, 2009 at 08:50 AM

avatar image

Paul Ward
109

(comments are locked)
10|1200 characters needed characters left

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]%'
more ▼

answered Oct 21, 2009 at 11:35 AM

avatar image

Bob Hovious
1.6k 5 9 13

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1066
x408

asked: Oct 21, 2009 at 08:18 AM

Seen: 11435 times

Last Updated: Oct 21, 2009 at 09:13 AM

Copyright 2016 Redgate Software. Privacy Policy