CODEWITHSUNDEEP
sqlpostgresql
user2201789
user2201789

Reputation: 1211

sql or postgresql to query table column with comma separator

PostgreSQL table1

id | columnA             | columnB
---+---------------------+--------
1  | A 01,A 02,A 03,A 04 | FALSE
2  | A 01,A 02           | FALSE
3  | A 01,A 02,A 03,A 04 | TRUE 
4  | A 01,A 02           | TRUE

I used to use below query to find out how many records:

SELECT *
FROM DB1.table1
WHERE columnA LIKE 'A 04,%'
  OR columnA LIKE '%,A 04,%'
  OR columnA LIKE '%,A 04'
  AND columnB = 'false'

SELECT *
FROM DB1.table1
WHERE columnA LIKE 'A 02,%'
  OR columnA LIKE '%,A 02,%'
  OR columnA LIKE '%,A 02'
  AND columnB = 'false'

Is there better way to just have one query to find which column has 'A 02' or 'A 04' and 'FALSE'?

Upvotes: 1

Views: 862

Answers (3)

Belayer
Belayer

Reputation: 14934

Your initial posted query indicates you're searching for text at the beginning of the string (A 04,), at the end of the string (,A 04) or in the middle of the string (,A 04,). These can simply be reduced to anywhere in the string or simply (A 04). Combining with the (A 02) predicate value and the columnB predicate we get

select *
  from db1.table1
 where not columnB
   and (columnA like '%A 02%' or columnA like '%A 02%');

But I reiterate having multiple values in the same column, or even same data in separate columns with in a row is very bad design. Ther should be in sepreate rows.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You should fix the data model! Storing multiple values in a single column is very bad -- and Postgres has many better alternatives than strings. That said, sometimes we are stuck with other people's really, really bad decisions.

You can use like for this purpose:

SELECT *
FROM DB1.table1
WHERE ',' || columnA || ',' ~ ',A 02,|,A 04,' AND
      NOT columnB   -- assuming this is boolean

For a single value, you would use:

WHERE ',' || columnA || ',' LIKE '%,A 02,%' AND
      NOT columnB   -- assuming this is boolean

Upvotes: 1

S-Man
S-Man

Reputation: 23766

demo:db<>fiddle

SELECT *
FROM
    mytable
WHERE
    columnB = false AND ARRAY['A 02', 'A 04', 'clinic 02'] && string_to_array(columnA, ',')
  1. Create an array out of your interesting values: ARRAY['value1','value2']
  2. Convert your string list into an array: string_to_array(list, ',')
  3. Use the && comparator to check if both arrays overlap, meaning if both arrays share at least one element.

Further reading: Postgres arrays

Note: You should think about normalizing your data instead of holding some string separated values in one column. Your current way could lead in many problems.

Upvotes: 1

Related Questions