How do I select all the rows where a varchar field contains non-digit characters?

Question

I want to find all instances in a table where a string field cannot be cast as a number. Is there a way to do like a "try" in t-sql so that I can get all the id's where the cast failed and delete the value?

Answer 1

select * from yourTable Where ISNUMERIC(yourField) = 0

Answer 2

Hello. I'm not a t-SQL user so please take this as a disclaimer. Sorry if it's not the perfect answer to your question but I use MySQL which can use Regular Expressions. Now depending upon what you actually aim to do.. this might not be your final solution but here is a quickie example of a query I could run:

SELECT * FROM table WHERE columnName REGEXP '^[0-9]+$'

But again not sure if t-sql can do this but from this link: http://www.sqlteam.com/article/regular-expressions-in-t-sql it seems like it does. Hopefully this is atleast a start to your solution.

Again, not sure completely what your end use is.. but If I wanted only numbers I would use some sort of cleansing before loading data into the database rather than cleaning up afterwards...I always find this the easier route. (You may have started with the data pre-existing though)

Hope this helps!