Question 191043 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Like query for comma separated numbers

I am trying to get data from a view which returns users name and tags (comma separated numbers). Now I want to get a user if it has a certain tag.

My view returns this:

 id |  name  |        tags        
----+--------+--------------------
  1 | Albert | 1,2,3,4
  2 | Isaac  | 11,22,33,40,50
  3 | Marie  | 44,110,220,330,500
  4 | Ray    | 111,222,333

I want to get a user which contains tag = 4. When I try to query using LIKE operation, it returns multiple users.

select * from users
  where tags LIKE '%4%';

Output:

 id |  name  |        tags        
----+--------+--------------------
  1 | Albert | 1,2,3,4
  2 | Isaac  | 11,22,33,40,50
  3 | Marie  | 44,110,220,330,500

Desired Output:

 id |  name  |        tags        
----+--------+--------------------
  1 | Albert | 1,2,3,4

How do I tackle this problem?

Online playground link: https://onecompiler.com/postgresql/3zc7pfngm

Tags:

2

Answers


  1. 0

    Try the below query :-

    SELECT *
FROM users
WHERE ',' || tags || ',' LIKE '%,4,%';
    Login or Signup to reply.
  2. 0

    Well, LIKE works in any database, but in PostgreSQL the string_to_array is your solution

    select *
from users
where 4 = any(string_to_array(tags, ',')::int[]);

    I assume that all your tags are integers. Adapt to text if not.

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

Back To Top
Search