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

JMasciantoni gravatar image

JMasciantoni
11 1 1 1

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

Kev Riley gravatar image

Kev Riley ♦♦
52.7k 47 49 76

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, 2009 at 11:53 AM Matt Whitfield ♦♦
oops! cheers for spotting that Matt, fixed now!
Oct 21, 2009 at 12:27 PM Kev Riley ♦♦
(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

Paul Ward gravatar image

Paul Ward
109

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, 2009 at 09:15 AM Kev Riley ♦♦
(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

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x985
x343

asked: Oct 21, 2009 at 08:18 AM

Seen: 5839 times

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