MS SQL

WHERE field has a numeric value?

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Apr 2006

Posts: 2

Reputation: Questor is an unknown quantity at this point

Solved Threads: 0

Questor

Offline

Newbie Poster

WHERE field has a numeric value?

Apr 17th, 2006

The table I'm searching is varchar. The entries I'm interested in are only numeric. But for some reason there's mixed string/int entries. How can I chose only the numeric values?

•

Join Date: Oct 2005

Posts: 523

Reputation: pty is on a distinguished road

Solved Threads: 37

pty

Offline

Posting Pro

Re: WHERE field has a numeric value?

Apr 17th, 2006

i dont use SQL Server much these days, but is there not a isNumber() or isNumeric() function?

If there isn't im sure writing your own wouldnt be that difficult. Just attempt to cast a varchar as a number and if it succeeds its a number. If it doesnt, it aint.

The 'real' solution to this is to fix your database so that text data is stored in char/varchar fields and numeric data is stored in the appropriate numeric type.

•

Join Date: Jul 2005

Posts: 483

Reputation: campkev is an unknown quantity at this point

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: WHERE field has a numeric value?

Apr 17th, 2006

pty is right. this should do it for you

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
 SELECT * FROM tablename WHERE isnumeric(columnname) = 1
 select * from tablename where isnumeric(columnname) = 1