using LOWER with IN condition

Question

assume I have a table named comodity_group and the structure looks like:

+----------+-------+
| group_id | name  |
+----------+-------+
| 1        | Data1 |
+----------+-------+
| 2        | Data2 |
+----------+-------+
| 3        | data3 |
+----------+-------+

and I have the following query

SELECT * FROM comodity_group WHERE name IN('data1','data2','data3')

the query return 0 result, because condition is all in lowercase (note that the condition is also dynamic, meaning it can be Data1 or daTa1, etc)

so I want to make both condition and field name in lowercase, in other word case insensitive.

score 10 · Answer 1 · answered Jan 17 '17 at 06:59

10

You can use ILIKE and an array:

select *
from comodity_group
where name ilike any (array['Data1', 'data2', 'dATA3']);

Note that this won't be really fast as the ILIKE operator can't make use of a regular index on the name column.

answered Jan 17 '17 at 06:59

a_horse_with_no_name

497,550
91
775
843

this is what I looking for. thank you very much! – Dariel Pratama Jan 17 '17 at 07:09
@DarielPratama You should mark this answer correct if it helped you. You can also upvote any other answers which you found useful. – Tim Biegeleisen Jan 17 '17 at 10:05
yep, I did that but I don't know my the check mark is still grey when it's supposed to be green. – Dariel Pratama Jan 18 '17 at 04:42
It also works when you want to look for a value in a set of columns. Example: SELECT * FROM table WHERE 'avalue' ilike any (array[table_column1, table_column2,...]): – Francisco M Nov 16 '21 at 11:46

score 1 · Answer 2 · edited Jan 17 '17 at 07:06

1

You can convert your name data to lowercase

SELECT * FROM comodity_group WHERE lower(name) IN('data1','data2','data3')

edited Jan 17 '17 at 07:06

Vivek S.

17,862
6
63
80

answered Jan 17 '17 at 06:55

Amit

3,010
2
25
31

Tim Biegeleisen · Answer 3 · 2017-01-17T07:08:07.157

1

Assuming you have control over the terms which appear in the IN clause of your query, then you should only need to lowercase the name column before making the comparison:

SELECT *
FROM commodity_group
WHERE LOWER(name) IN ('data1', 'data2', 'data3')

Off the top of my head, you could also join to an inline table containing the search terms:

WITH cte AS (
    SELECT 'daTa1' AS name
    UNION ALL
    SELECT 'Data2'
    UNION ALL
    SELECT 'datA3'
)
SELECT *
FROM commodity_group t1
INNER JOIN cte t2
    ON LOWER(t1.name) = LOWER(t2.name)

With the possible matches in an actual table, we now have the ability to lowercase both sides of the comparison.

edited Jan 17 '17 at 07:08

answered Jan 17 '17 at 06:56

Tim Biegeleisen

451,927
24
239
318

the condition alone can be dynamic like `IN('daTa1','Data2','datA3')`. I can't control the terms in the `IN` clause. – Dariel Pratama Jan 17 '17 at 06:57
Are you using dynamic SQL? Update your question to make this clear. – Tim Biegeleisen Jan 17 '17 at 06:58
@TimBiegeleisen: it's already in the question "*note that the condition is also dynamic, meaning it can be Data1 or daTa1, etc)*" – a_horse_with_no_name Jan 17 '17 at 07:00

using LOWER with IN condition

3 Answers3

Linked