• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Case insensitive like in postgresql

 
D Rog
Ranch Hand
Posts: 472
Linux Objective C Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I use something like
select name from employees where name like 'U%'
It works great to get all employees with name starting from 'U'. However some names started from 'u' (lower case) and do not get listed. How it can be solved? using union doesn't look like smart. PostGreSQL documentation states that I can use regular expression, but they do not work. I do not know why oracle style is working. Any help?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It is database dependent whether a "like" is case sensitive. I don't know about regular expressions in PostGreSQL, but there are three database independent approaches.

1) Union - I realize you stated this doesn't work. I'm mentioning it anyway because it should work so maybe the syntax is different.
select name from employees where name like 'U%'
union
select name from employees where name like 'u%'

2) Or
select name from employees where name like 'U%' or name like 'u%'

3) Uppercase
select name from employees where to_upper(name) like 'U%'
 
Jan Cumps
Bartender
Posts: 2584
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would go for Jeanne's option 3. This will return your data in one run.

1 and 2 would execute two queries (an OR clause is implicit the same as a UNION).

Regards, Jan
 
D Rog
Ranch Hand
Posts: 472
Linux Objective C Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, option 3 looks good.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic