6

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.

Dariel Pratama
  • 1,607
  • 3
  • 18
  • 49

3 Answers3

11

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.

  • 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
  • This is what I needed but also has to add the percent sign before and after each element, for my case. so i had: select * from comodity_group where name ilike any (array['%Data1%', '%data2%', '%dATA3%']); – mr nooby noob Sep 08 '22 at 07:40
1

You can convert your name data to lowercase 

SELECT * FROM comodity_group WHERE lower(name) IN('data1','data2','data3')
Vivek S.
  • 19,945
  • 7
  • 68
  • 85
Amit
  • 3,662
  • 2
  • 26
  • 34
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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360