Detect If Value Is Number in MySQL

Detect if value is number in MySQL

This should work in most cases.

SELECT * FROM myTable WHERE concat('',col1 * 1) = col1

It doesn't work for non-standard numbers like

  • 1e4
  • 1.2e5
  • 123. (trailing decimal)

How do I check to see if a value is an integer in MySQL?

I'll assume you want to check a string value. One nice way is the REGEXP operator, matching the string to a regular expression. Simply do

select field from table where field REGEXP '^-?[0-9]+$';

this is reasonably fast. If your field is numeric, just test for

ceil(field) = field

instead.

MySQL: How to check if a provided value is a number/ integer or letters?

You can use a REGEXP.

AND CASE
WHEN ('7' REGEXP '^[0-9]+$') THEN
p.parent_id != p.page_id
ELSE p.parent_id = p.page_id
END

The reason your CONVERT() fails to work as expected is that a non-numeric string will always be cast as 0, rather than NULL.

mysql> SELECT CONVERT('7a', SIGNED INTEGER);
+-------------------------------+
| CONVERT('7a', SIGNED INTEGER) |
+-------------------------------+
| 7 |
+-------------------------------+

mysql> SELECT CONVERT('self', SIGNED INTEGER);
+---------------------------------+
| CONVERT('self', SIGNED INTEGER) |
+---------------------------------+
| 0 | <-- non-numeric strings always cast to 0
+---------------------------------+

Your method may work if you compare against 0 instead of NULL, but I don't have a good way to test. However, this fails if the value actually is 0, wherein you'll get the wrong comparison back.

AND IF(CONVERT('7', SIGNED INTEGER) <> 0, p.parent_id != p.page_id, p.parent_id = p.page_id)

MySQL how to check if value starts with letter and ends with a number?

You should use REGEXP, WHERE fields REGEXP regex_string. Also, you don't have to write all of the alphabet letter, you could just write it like [a-zA-Z].

To be specific: a range is a contiguous series of characters, from low
to high, in the ASCII character set.[101] For example, [z-a] is not a
range because it's backwards. The range [A-z] matches both uppercase
and lowercase letters, but it also matches the six characters that
fall between uppercase and lowercase letters in the ASCII chart: [, \,
], ^, _, and '.

https://docstore.mik.ua/orelly/unix3/upt/ch32_08.htm

SELECT * FROM hotelroom
WHERE city LIKE '%Cansas%' AND
key REGEXP '^[a-zA-Z].*[0-9]$'

Also,

I want to show specific data where all the values start with a letter
and ends with a number in the city of cansas only.

If you only want to get the result from the city of Cansas, you don't have to use LIKE. If you're using LIKE, it will also match Cansas2 city or anything that has Cansas as it's substring.

You could just use equals (=) operator.

SELECT * FROM hotelroom
WHERE city = 'Cansas' AND
key REGEXP '^[a-zA-Z].*[0-9]$'

Check if a string contains numbers

Check out the MySQL Regex methods. Something like the following should work.

SELECT * FROM table WHERE tag REGEXP '[0-9]'

Check if a number exists with a range in DB

I was able to solve this using this query below, it takes care of all the four scenarios mentioned above.:

  SELECT count(StartingNumber) FROM range
WHERE 690 BETWEEN StartingNumber and EndingNumber
or 1800 BETWEEN StartingNumber and EndingNumber
or StartingNumber in ( SELECT StartingNumber
FROM range
WHERE StartingNumber BETWEEN 690 AND 1800);

How to check exact integer value in string mysql

If the pattern is same i.e. the amount is at the end and separated with a space you can use substring_index function

mysql> select substring_index('BUY ABOVE 200',' ',-1) as amount;
+--------+
| amount |
+--------+
| 200 |
+--------+
1 row in set (0.00 sec)

So you may do the query as

select * from table where 
substring_index(col_name,' ',-1) = '200'

Also you can use rlike for matching

mysql> select 'BUY ABOVE 200' rlike '[[:<:]]200[[:>:]]';
+-------------------------------------------+
| 'BUY ABOVE 200' rlike '[[:<:]]200[[:>:]]' |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select 'BUY ABOVE 2000' rlike '[[:<:]]200[[:>:]]';
+--------------------------------------------+
| 'BUY ABOVE 2000' rlike '[[:<:]]200[[:>:]]' |
+--------------------------------------------+
| 0 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select 'BUY 300 ABOVE' rlike '[[:<:]]200[[:>:]]';
+-------------------------------------------+
| 'BUY 300 ABOVE' rlike '[[:<:]]200[[:>:]]' |
+-------------------------------------------+
| 0 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select 'BUY 200 ABOVE' rlike '[[:<:]]200[[:>:]]';
+-------------------------------------------+
| 'BUY 200 ABOVE' rlike '[[:<:]]200[[:>:]]' |
+-------------------------------------------+
| 1 |
+-------------------------------------------+

So say you have data as

  • BUY ABOVE 200
  • BUY ABOVE 200 AND more
  • 200 And more buy
  • BUY ABOVE 2000
  • BUY ABOVE 300

and you would like to select matching only 200 , the query would be

select * from table where
colname rlike '[[:<:]]200[[:>:]]'

MySQL How to check if a value is an integer or a text in a field

A text type column always treats its values as text (a string), so regardless of what value is stored, you need to enclose it in '. MySQL can compare a number as a string.

Also, is_int is not suitable for checking $_GET values, as they are considered strings. Use is_numeric instead.

But notice the comments to your question, a redesign is more suitable if you need to store integers and/or strings separately in your database.

Checking rows that are not numbers in a varchar column for MySQL Query

try this

    SELECT * FROM myTable WHERE concat('',col1 * 1) != col1

demo here



Related Topics



Leave a reply



Submit