Excluding alphanumeric fields in a query

[johnpienaar]

Hi

I am trying to filter a query to exclude any rows where the Identity Number field has any alphanumeric (not sure if this is the right word) values in it. eg:
i would want to exclude rows where the Identity Number field is '123fgh345' or 'sdf123456' only keeping rows where there are numbers only eg '123456789' the values are currently stored as varchar.

help would be greatly appreciated.

 

[amorous]

Instead of excluding the id's containing alphanumeric...include the id's containing only numeric using the condition

isnumeric(yourid)=1

Thanks

-Vj

 

[johnpienaar]

thanks so much amorous- worked like a charm.

 

[nigelrivett]

Careful - isnumeric allows fields that conatain alphabetic characters - as long as they can be converted to a numeric datatype (e.g. 123e2). As such it's not a lot of use.

try

patindex('%[^0-9]%', col) = 0
To allow only digits. You might want to allow spaces and decimal points.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[ESquared]

LIKE '%[^0-9]%'

Seems more straightforward than patindex, but it's the same concept.

IsNumeric also blows it on strings like ',' which it calls numeric but cannot be converted to ANY numeric data type.

 

[nigelrivett]

',' can be converted to money.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[amorous]

I am beginner and I suggested something that i knew....but never knew that it has lot of loopholes ...

I get to learn a lot here...you guys are awesome..

Thanks

-VJ

 

[ESquared]

Thanks nigelrivett, I see you are correct.

IsNumeric tries to be universal and in so doing fails for every specific.

 

[johnpienaar]

hi thanks all for the replies

just an update- i tried both of the new methods- the (LIKE '%[^0-9]%') didn't seem to work at all, but nigelrivett's (patindex('%[^0-9]%', col) = 0) worked perfectly. thanks for pointing out the flaw in isnumeric.

 

[nigelrivett]

should be
where xxx not LIKE '%[^0-9]%'
to find numerice.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.