Testing if column value is of type INT

[jasonsalas]

Hi everyone,

I'm hoping someone can help me out with a problem. I'm trying to run a SPROC that needs to return data only if a column of type VARCHAR(200) can be converted to type INT. Basically, I've got varying types of data within the column from URLs, including record IDs, and I need to return only the record IDs. I'm thinking that somehow evaluating whether a column can be considered an INT, and ignoring all other values, might be the way to go.

Is there a conversion or some other function I can call that won't break the rest of the data?

Thanks for your help!

 

[Otto]

isnumeric(expr): returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type;
If isnumeric() then you can cast it into numeric.
If you want only int, you may check the existence of '.' in the expression...

 

[jasonsalas]

Thanks! It worked out. I really appreciate the help.

 

[gmmastros]

There can exist certain data that may prevent a successful conversion to int. I wrote a faq's about this. faq183-6423

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom