Check for numeric values
Hello, 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? MySQL version: 5.5 Thank you, Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Custom Programming Web Hosting Services http://www.thunder-rain.com/ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Check for numeric values
Hi, 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', 'test', '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 'test' and '111test' and '111' http://stackoverflow.com/questions/5064977/detect-if-value-is-number-in-mysql Regards. On Tue, Oct 8, 2013 at 7:53 AM, Mike Blezien mick...@frontiernet.netwrote: Hello, 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? MySQL version: 5.5 Thank you, Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-**=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Custom Programming Web Hosting Services http://www.thunder-rain.com/ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=**-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Check for numeric values
Thank you this is very helpful and was what I was looking for. Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Custom Programming Web Hosting Services http://www.thunder-rain.com/ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - Original Message - From: Sukhjinder K. Narula To: Mike Blezien Cc: MySQL List Sent: Tuesday, October 08, 2013 8:08 AM Subject: Re: Check for numeric values Hi, 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', 'test', '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 'test' and '111test' and '111' http://stackoverflow.com/questions/5064977/detect-if-value-is-number-in-mysql Regards. On Tue, Oct 8, 2013 at 7:53 AM, Mike Blezien mick...@frontiernet.netwrote: Hello, 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? MySQL version: 5.5 Thank you, Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-**=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Custom Programming Web Hosting Services http://www.thunder-rain.com/ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=**-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
