Is there an easier way to rewrite SQL Server queries for PostgreSQL?

Question

In particular, I'm looking for a way to more easily rewrite my like queries without having to use the "lower" function each time.

In SQL Server, my query would look like this:

WHERE (FIELD LIKE '%Foot%Locker%' or FIELD LIKE '%Foot%Action%' or FIELD LIKE '%Champs%')

In PostgreSQL, I must rewrite each query as such (if I want my query to capture both Foot Locker AND foot locker and any other caps driven permutation):

WHERE (lower(FIELD) LIKE lower('%foot%locker%') or lower(FIELD) LIKE lower('%foot%action%') or lower(FIELD) LIKE lower('%champs%')

This, of course, is very annoying. I have to rewrite 100s of queries. Is there an easy workaround?

Gordon Linoff · Accepted Answer · 2016-05-24 12:22:24Z

4

Postgres has just what you are looking for ilike:

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

(Documented here.)

So, you can do something like:

WHERE (field ILIKE '%foot%locker%' or FIELD ILIKE '%foot%action%' or FIELD) ILIKE '%champs%'

Or, if you prefer:

WHERE lower(field) ~ '(foot.*locker)|(foot.*action)|champ'

answered May 24, 2016 at 12:22

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

you actually don't need the lower() if you use ~* instead of ~

Collectives™ on Stack Overflow

Is there an easier way to rewrite SQL Server queries for PostgreSQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related