Bring back just numeric

Hi,


Is there a sql statement that will bring back all records that are just numeric? A database column has both numeric and string values stored in it. I need to bring back just the numeric values.

What database system?

Then it ONLY has strings in it, because the column would have to be a text or char type. You must perform tests to find out which values are candidates for conversion to numeric. Are you looking for integers, or integers and floating-point values?

Almost every database system out there has operators for casting values of one type to another type. One simple test you could do is simply cast the value to INT or FLOAT in your query, and if the resulting value is non-zero, then in must have been a numeric one. However, this doesn't answer some other questions: Are there limits to the size you will accept? what if you have both string and numeric characters in one row? Will you accept just the numeric part as a value, or does ANY other string content disqualify the row? What about multiple numbers in a rwo?

I don't know why you would need this sort of query, or even why the database has been designed in such a way as to need this query. If the values in this column are significant to the logic of your application, surely you can design the database in a more efficient and logical way. Two different types of data should be in two different columns. or perhaps even in two different tables.

Thanks for your replies,

It is a MYSQL database and I am using PHP. You're right rycamor about having two different types of data in the same column, unforunetly, I didn't design the database so I don't have much control over that.

The column is set as varchar100

I was thinking along the lines of something like this in the record:

"I said 20, she said 21"

and now they want to pull just the records with numbers in the text. And the solution I was thinking is:

Regular Expressions! Hooray!

Disclaimer: I don't use MySql much, and I don't know anything about the RegExp engine it uses.... it also doesn't solve many of the problems Rycamor brought up.

ya know, i've seen that mysql page before, and it sure looks like all it can do is search -- i.e. return a true/false indicator to show whether the string actually matches the regular expression

i have not seen any examples where the mysql regexp is used to modify a string, e.g. strip non-numerics out

anyone know for sure?


rudy