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
Sql: Update Column With Increment Numbers Based on 2 Columns
Presto SQL - Converting a Date String to Date Format
Select to Get Rows Based on Minimum Value of a Column
Sql Select Everything After a Certain Character
Error:More Than One Row Returned by a Subquery Used as an Expression
Job for Mysqld.Service Failed See "Systemctl Status Mysqld.Service"
Use Multiple Conflict_Target in on Conflict Clause
Comma Separated Values in SQL Server Returning Duplicates
Run a Join Statement That Excludes Duplicate Rows
How to Use SQL Like Condition With Multiple Values in Postgresql
Convert Utc Milliseconds to Datetime in SQL Server
How to Get Textual Contents from Blob in Oracle SQL
Mysql: Alter Table If Column Not Exists
Finding the Highest Average Salary in SQL
Remove Multiple Keys from Jsonb Column in One Statement