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.

Solution 1

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%')

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

Since another answer suggested it: never use SIMILAR TO:

• Using SIMILAR TO for a regex?
• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Solution 2

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.

Solution 3

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

Comments

[Muhammad Taqi]

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

[eggyal]

@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]

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