top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to check for numeric values in mysql?

+1 vote

I need to select some data from a table where a column is not a numerical value but can't seem to get the right syntax for this.

Basically we need to do something like this:

SELECT * FROM tablename WHERE column_name (IS NOT A NUMERIC VALUE)

what is the correct syntax to accomplish this?

posted Oct 8, 2013 by Satish Mishra

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

You could use regular expression to do this, here is the example with the reference site that might help you:

If your data is 'test', 'test0', 'test1111', '111test', '111'

SELECT * FROM myTable WHERE col1 REGEXP '^[0-9]+$';
Result: '111'

In regex ^ mean begin, and $ - end.
SELECT * FROM myTable WHERE col1 REGEXP '^[0-9]+.?[0-9]*$'; - for 123.12

But, select all records where number exists:

SELECT * FROM myTable WHERE col1 REGEXP '[0-9]+';
Result: 'test0' and 'test1111' and '111test' and '111'

answer Oct 8, 2013 by Garima Jain
Similar Questions
+1 vote

Joiner using how to get three tables value fetching from single query.

+1 vote

My issue is that I have a PHP FORM that is submitting values through more than 55 input-text-fields, into a MySQL table.

Now, when I use UPDATE feature, suppose for e.g for 3 fields:

$sql="UPDATE pathology_test_rates SET bsugar_random='$bsugar_random', bsugar_fasting='$bsugar_fasting', bsugar_pp='$bsugar_pp'";

And then , if I want to update only 1 value, out of above 3, using PHP FORM, its making other 2 values empty. Whereas, I want other 2 values to remain un-disturbed.

How to do this ? Please help. M not using id in table.