Have Geek Brain

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

I want to find all instances in a table where the row field cannot be written as a number. Is there a way to do how to "try" in t-sql so that I can get the entire ID that was failing and remove the value?

+1


a source to share


2 answers 


select *
from yourTable
Where ISNUMERIC(yourField) = 0


+4


a source


I'm not a t-SQL user, so please take this as a disclaimer. Sorry if this is not a perfect answer to your question, but I am using MySQL which can use regular expressions. Now depending on what you actually intend to do .. this may not be your final solution, but here's a quick example of a query I could run:

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

But again not sure if t-sql can do it, but from this link: http://www.sqlteam.com/article/regular-expressions-in-t-sql it seems like it does. Hopefully this will be the beginning of your decision.



Again, not entirely sure what your end use is ... but if I only wanted numbers, I would use some cleanup before loading the data into the database, rather than cleanup after that ... I always find it to be easier route. (You may have started with pre-existing data)

Hope this helps!

+1


a source






More articles:


All Articles