CODEWITHSUNDEEP
postgresqlcase
Mankind1023
Mankind1023

Reputation: 7732

Postgres - LIKE ANY UPPER()

Is there a way to use UPPER with a LIKE ANY()?

I have the following example:

SELECT
....
where skus.number like any ('{"%00130204%", "%00130202"}')

Unfortunately the skus I'm checking here can be of different cases, so I tried doing this:

SELECT
....
where UPPER(skus.number) like any UPPER('{"%00130204%", "%00130202"}'))

Which doesn't work, is there any way to get this working in the query itself?

Upvotes: 1

Views: 2863

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51609

along with being totally with @Joe on his answer as better for you query (and skipping phylosophy behind idea to represent digits in uppercase), I decided to answer the topic of your post

Is there a way to use UPPER with a LIKE ANY()?

yes - here it is:

t=# select UPPER('110013020411') like any (UPPER('{"%00130204%", "%00130202"}')::text[]) comaprison;
 comaprison
------------
 t
(1 row)

after you upper text in array represented as text you need to cast it back inorder to use with ANY (array)

Upvotes: 0

Joe Love
Joe Love

Reputation: 5972

No need to use upper. Use the case insensitive version of like, "ilike" instead.

 SELECT
 ....
 where skus.number ilike any ('{"%00130204%", "%00130202"}')

Upvotes: 3

Related Questions