CODEWITHSUNDEEP
mysqlsql
Raj Tandel
Raj Tandel

Reputation: 19

Getting row which contain exact text

I have a Table named Test. In which I have 3 columns.It looks like

imei is of type int, V_Id is of type int and app_no is of type text.

+-----------------+------------+------------+
|   imei_no       | V_Id      | app_no     | 
+-----------------+------------+------------+
|      7781112889 |       496 | 14,25      |
|     70433357641 |        498 | 125        |
|     70433357641 |        498 | 25         |
|     70433357641 |        498 | 254        |
|     70433357641 |        498 | 16,17,25   |
---------------------------------------------

i want to get rows which contains app_no=25. I have used query containing LIKE and IN. Meaning %25%--- But it will also give me record where app_no=125 and app_no=254. I have also tried "like app_id='25'" But it will exclude record where app_id=14,25.

Is there any other way by which I can get my desire record which contain all rows having app_id=25 including records like app_id=14,25. But excluding record where app_id=254,125.

Upvotes: 0

Views: 54

Answers (4)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can also use a simple REGIX like:

SELECT
  yourField,
  youtField REGEXP('^25$|^25,|,25$')
FROM youtTable;

sample

MariaDB [yourschema]> SELECT t,t REGEXP('^25$|^25,|,25$') FROM l;
+-------+----------------------------+
| t     | t REGEXP('^25$|^25,|,25$') |
+-------+----------------------------+
| 1     |                          0 |
| 2     |                          0 |
| 1,25  |                          1 |
| 1,125 |                          0 |
| 125,1 |                          0 |
| 25    |                          1 |
| 25,0  |                          1 |
+-------+----------------------------+
7 rows in set (0.00 sec)

MariaDB [yourschema]>

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522732

Use FIND_IN_SET

SELECT *
FROM test
WHERE FIND_IN_SET('25', app_no) > 0

I believe you can also do this using only the LIKE operator. Assuming that your CSV list of app numbers have no spaces:

SELECT *
FROM test
WHERE app_no LIKE '25' OR
      app_no LIKE '25,%' OR
      app_no LIKE '%,25' OR
      app_no LIKE '%,25,%'

Upvotes: 1

Dhaval Bhavsar
Dhaval Bhavsar

Reputation: 495

please use find in set 

select * from <table_name> where FIND_IN_SET(app_no,'25')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

First, you should fix your data structure. Storing lists of ids in a string is a bad idea for many reasons:

  • Numbers should be stored as numbers, not strings.
  • A column should have a single value.
  • Ids should be declared using foreign key relationships.
  • SQL (in general) does not have very good support for string functions.
  • Queries on strings cannot use indexes, drastically reducing performance in some cases.

The SQL'ish way to store such data is in a separate table, with one row per imei_no and one per app_no. This would be a junction table.

Sometimes, we are stuck with other people's really bad design decisions. In that case, you can use find_in_set():

on find_in_set(25, app_no)

Upvotes: 4

Related Questions