SQL query to match one of multiple strings

Question

I have following data in table:

+----------------------+----------------------------------------------------------+--------------+
| subscriber_fields_id | name                                                     | field_type   |
+----------------------+----------------------------------------------------------+--------------+
|                  143 | Peshawar/Islamabad/Lahore/Swat/Mardan/Karachi            | Job Location |
|                  146 | Karachi                                                  | Job Location |
|                  147 | Lahore and Karachi                                       | Job Location |
|                  149 | Karachi, Mirpur Khas, Sukkur, Layyah, Gilgit, Charsaddah | Job Location |
|                  152 | Islamabad or Lahore                                      | Job Location |
|                  155 | Islamabad                                                | Job Location |
|                  157 | 7 Districts of Sindh and Karachi                         | Job Location |
+----------------------+----------------------------------------------------------+--------------+

My query is:

select * from subscriberfields
where  name like '%Khairpur,Islamabad,Karachi%';

Result:

+----------------------+-----------------------------------------------+--------------+
| subscriber_fields_id | name                                          | field_type   |
+----------------------+-----------------------------------------------+--------------+
|                  143 | Peshawar/Islamabad/Lahore/Swat/Mardan/Karachi | Job Location |
|                  152 | Islamabad or Lahore                           | Job Location |
|                  155 | Islamabad                                     | Job Location |
+----------------------+-----------------------------------------------+--------------+

It should return all rows where the name includes Islamabad, Khairpur or Karachi but it is not.

You want WHERE name LIKE '%Khairpur%' OR name LIKE .... However, it is not sargable and therefore will require a full table scan—it will become desperately inefficient as the table size grows. You should consider using fulltext search instead. — eggyal
– eggyal, Commented Mar 9, 2015 at 9:52
Have you tried SIMILAR TO like I've suggested in the answer? — benscabbia
– benscabbia, Commented Mar 9, 2015 at 10:22
@gudthing: Never use SIMILAR TO, it's an utterly pointless construct - there is always a better option. I added an answer. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 9, 2015 at 13:04

Erwin Brandstetter · Accepted Answer · 2023-08-12 01:44:14Z

For a proper solution, either normalize your database design or, barring that, consider full text search.

For a quick solution to the problem at hand, use a regular expression match (~) or three simple LIKE expressions:

SELECT *
FROM   subscriberfields 
WHERE  name ~ '(Khairpur|Islamabad|Karachi)';

Or:

WHERE (name LIKE '%Khairpur%'
    OR name LIKE '%Islamabad%'
    OR name LIKE '%Karachi%')

.. which can be compressed like Mario demonstrates in his answer. Or shorter, yet, with an array literal:

WHERE name LIKE ANY ('{%Khairpur%, %Islamabad%, %Karachi%}');

But the regexp match gets the same query plan in modern Postgres (can use the same trigram index) and is shorter, yet. See:

PostgreSQL LIKE query performance variations

Or use ~* or ILIKE for case-insensitive matching.

Since another answer suggested it: never use SIMILAR TO:

Mario Leonel · Accepted Answer · 2018-07-17 20:58:01Z

5

You can use this:

select * from subscriberfields
where  name like any(array['%Khairpur%','%Islamabad%','%Karachi%']);

https://postgres.cz/wiki/PostgreSQL_SQL_Tricks#LIKE_to_list_of_patterns

answered Jul 17, 2018 at 20:58

Mario Leonel

511 silver badge3 bronze badges

Comments

benscabbia · Accepted Answer · 2015-03-09 13:12:00Z

3

Try using SIMILAR TO like below:

SELECT * FROM subscriberfields 
WHERE name SIMILAR TO '%(Khairpur|Islamabad|Karachi)%';

Also you should read up on database normalization. Your design could and should definitely be improved.

edited Mar 9, 2015 at 13:12

answered Mar 9, 2015 at 9:55

benscabbia

18.2k13 gold badges54 silver badges62 bronze badges

Comments

Mironek · Accepted Answer · 2023-05-12 12:38:03Z

In MySQL try using a regular expression "REGEXP" and operator | which works like OR:

SELECT * FROM subscriberfields  WHERE  name REGEXP 'Khairpur|Islamabad|Karachi';

so you will get results containing at least one of these words.

Alternatively, if you want results that will include ALL words, use:

SELECT * FROM subscriberfields  WHERE  name REGEXP '(?=.*Khairpur)(?=.*Islamabad)(?=.*Karachi)';

Works with MySQL on server version 10 MariaDB and PHP 8

If you want to learn more about REGEXP try:

https://www.geeksforgeeks.org/mysql-regular-expressions-regexp/ https://www.freecodecamp.org/news/sql-contains-string-sql-regex-example-query/

There are many blogs with REGEXP examples, but not everyone will give you all the patterns and values you can use, but for starters, have a look at these.

BabyDuck · Accepted Answer · 2015-03-09 09:47:52Z

-1

Use OR in WHERE clause, like,

select * from subscriberfields where name like '%Khairpur%' OR name like '%Islamabad%' OR name like '%Karachi%';

Hope it works.

answered Mar 9, 2015 at 9:47

BabyDuck

1,2491 gold badge9 silver badges22 bronze badges

3 Comments

Muhammad Taqi Over a year ago

no, the value is coming from database, i cannot split it by commas because sometimes it is split by , , /, - etc.

eggyal Over a year ago

@MTaqi: This sounds like very poor database design indeed. You may like to read up to database normalization and also take a look at Is storing a delimited list in a database column really that bad?

BabyDuck Over a year ago

In that case you really need to consider FULLTEXT Searching, as suggested by eggyal.

Collectives™ on Stack Overflow

SQL query to match one of multiple strings

5 Answers 5

Comments

Comments

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related