SELECT with case sensitive ANY

Question

I have a table with column of arrays that contains words in different cases. I can select all rows with required tag like this:

SELECT * FROM table WHERE 'tag' = ANY(tags::TEXT[]);

but how to take into account that words in arrays can also be in upper case?

UPDATE

that's how can I get all unique values from column of arrays:

 SELECT DISTINCT LOWER(unnest)
        FROM (SELECT unnest(tags) FROM table) AS all_tags;

maybe it will be helpful

How do you want to "take them into account"? It's unclear what you're asking. — lurker, Dec 18 '17 at 20:12
@lurker I'm looking for 'tag' but cannot find any of {'Tag', 'TAG', 'tAg' } — shmnff, Dec 18 '17 at 20:16
Possible duplicate of [PostgreSQL case insensitive SELECT on array](https://stackoverflow.com/questions/16311289/postgresql-case-insensitive-select-on-array) — Demo, Dec 18 '17 at 20:28

score 2 · Accepted Answer · answered Dec 18 '17 at 20:23

2

select *
from my_table
where 'tag' ilike any(tags)

Per the documentation:

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale.

answered Dec 18 '17 at 20:23

klin

112,967
15
204
232

I don't understand. Try it here: [SqlFiddle](http://sqlfiddle.com/#!17/7d51f/1) and show what doesn't work (paste the link of the modified example). – klin Dec 18 '17 at 21:07
doesn't work if upper and lower cases contains in one array – shmnff Dec 18 '17 at 21:15
What do you mean - doesn't work? See [SqlFiddle](http://sqlfiddle.com/#!17/48f71/1) – klin Dec 18 '17 at 21:18

score 0 · Answer 2 · answered Dec 18 '17 at 21:14

0

this query finds all

SELECT * FROM (SELECT *, lower(unnest(tags)) AS val FROM table) x WHERE val = 'c++';

answered Dec 18 '17 at 21:14

shmnff

647
2
15
31

score -1 · Answer 3 · answered Dec 18 '17 at 20:16

You need to make a choice and choose to set tag field to be upper case or not, and the content of your array also in the same state. For this you can save the tag, or update your actual content to upper or lower case and define an index on this field and then set the array content to the same state.

SELECT with case sensitive ANY

3 Answers3