Question 246608 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Check if a number is a digit in a number column SQL

I have a table with a column of DR. ID #’s. I want all the rows that have a 2 in them.

first_name dr_id
joe 534
eric 267
allen 309
steven 125
gary 11

I have been searching for a number version of substring but I can’t find it.
The only other idea would be to change the values to string then do a substring search. Is there another way?

Tags:

3

Answers


  1. 0

    I suppose a number’s inherent trait is it’s eligible for mathematical operations. So if you want to define a search condition based on a number’s numeric form, then make sure it’s possible to define the search condition by performing a mathematical calculation on it. For instance, if we want to find a number which can be divided by 2,we can use WHERE num mod 2 =0 and num<>0 .

    But in this case, I’m not sure the condition find a number which has a 2 in it can be proved by a math operation. Therefore, using a string function(or REGEXP) is probably the only choice. Here I would suggest using locate(substr,str) funtion, which returns 0 if substr is not in str or NULL if any argument is NULL:

    select * from tb where locate('2',dr_id)>0;
    Login or Signup to reply.
  2. 0

    You have to use string functions, where the number will be automatically be casted to a string/char. e.g:

    SELECT 123 LIKE "%2%" CONTAINS_2;
+------------+
| CONTAINS_2 |
+------------+
|          1 |
+------------+

SELECT LOCATE("3", 123) > 0 CONTAINS_3;
+------------+
| CONTAINS_3 |
+------------+
|          1 |
+------------+

SELECT INSTR(123, "1") > 0 CONTAINS_1;
+------------+
| CONTAINS_1 |
+------------+
|          1 |
+------------+
    Login or Signup to reply.
  3. 0

    ou can achieve this by using the LIKE operator in SQL, assuming your DR. ID numbers are stored as strings. You can use the % wildcard to search for the digit "2" within the string. Here’s an example query:

    SELECT *
FROM your_table
WHERE dr_id LIKE '%2%';

    This query will return all rows where the dr_id column contains the digit "2" anywhere in the string.

    If your DR. ID numbers are stored as integers, you can use the CAST function to convert them to strings before performing the LIKE operation:

    IMPORTANT

    SELECT *
FROM your_table
WHERE CAST(dr_id AS VARCHAR) LIKE '%2%';

    This will cast the dr_id column as a string, and then you can perform the substring search using LIKE.

    Choose the appropriate query based on the data type of your dr_id column.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search