filter out values are not integer or null

Question

filter out values are not integer or null

sqlfriend

SSC Guru

Points: 52781
More actions
July 30, 2012 at 10:34 am

#278758

We have a table data from other source. One of the columns Model is varchar(5), most of values are like integer, values for example like 15, 80. others are null.
Recently a value like like a varchar 'F' was imported in.
We want to filter out it.
So I want to use select * from the table where the model is either an integer or null.
Is there a way to do that?
Thanks

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

select * from the table where the model not like '%[^0-9]%'

that will filter out anything that has anything that is not in the range of 0-9 characters; so punctuuation, or letters eliminate the results, and nulls are automatically excluded as well

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!

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thanks, it is very helpful. What if I want to include Null?

Also is this doing the same thing?

select * from table where ISNUMERIC(MODEL)=1 OR Model IS null

Thanks

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

And I got the same result.

But for the isNumberic option, what I am wondering is the column data type is varchar, how come the result of isNumeric turns true for values like 18, 40, although they look like an integer but the data type is varchar. Does it mean there is an implicit conversion when doing the isNumberic check?

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 4

IsNumeric tests to see if the string matches any of a number of specific patterns for all sorts of data types.

select isnumeric('3e0'),isnumeric('3,4'),isnumeric('$23,454')

'18' is certainly convertable an integer, for example...did you think it should not?

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!

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 5

ISNUMERIC will return a 1 if the value that is being tested can be converted to a int, bigint, smallint, tinyint, decimal, numeric, money, smallmoney, float, or real datatype. Be careful with it becuase if you are just looking for values that could be int in your data, you are likely to get some false positives.

This code also shows some things a person may not expect from the function:

select ISNUMERIC('1e1'), ISNUMERIC('-$,0.1'), ISNUMERIC('100' + 'e0'), ISNUMERIC('1e1' + 'e0')

http://msdn.microsoft.com/en-us/library/ms186272(v=sql.105).aspx

sqlfriend SSC Guru Points: 52781 More actions · Answer 6

roryp 96873 (7/30/2012)
ISNUMERIC will return a 1 if the value that is being tested can be converted to a int, bigint, smallint, tinyint, decimal, numeric, money, smallmoney, float, or real datatype. Be careful with it becuase if you are just looking for values that could be int in your data, you are likely to get some false positives.

The underline part is clear to answer my question. Thanks

so using below is better option?

select * from the table where the model not like '%[^0-9]%' or model is null

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 7

sqlfriends (7/30/2012)
roryp 96873 (7/30/2012)
ISNUMERIC will return a 1 if the value that is being tested can be converted to a int, bigint, smallint, tinyint, decimal, numeric, money, smallmoney, float, or real datatype. Be careful with it becuase if you are just looking for values that could be int in your data, you are likely to get some false positives.
The underline part is clear to answer my question. Thanks
so using below is better option?
select * from the table where the model not like '%[^0-9]%' or model is null

Yes, I would say Lowell's solution will work better than isnumeric for you. It really depends on your data though, if you have a value of 18.0, that code will skip over it. If you don't have data like that, or don't want things with a .0 at the end to be considered ints in your case, you should be good.