Monday, November 5, 2012

mysql cheat to check is number

Is that value a number or a string?

select cast("2apple" AS UNSIGNED), cast("3 bananas" AS UNSIGNED), cast(0+ "4 cars" AS UNSIGNED), cast("100" AS UNSIGNED), cast("BAR" AS UNSIGNED), "5 bears" regexp "[A-Za-z]", "6" regexp "[A-Za-z]"

2, 3, 4, 100, 0, 1, 0
Summary: Best way to check if the value is (only) a number is to check if it doesn't contain letters (assuming that letters are adequate for this detection. If you're expecting symbols but no letters, you'll need to test for the symbols).But, if you want to get the "Value" of the string (numbers before letters), CAST it as unsigned.If you know there will *never* be numbers before characters, you can use the CAST and test if greater than zero.

No comments:

Blog Archive