• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Case insensitive like in postgresql

 
Ranch Hand
Posts: 472
Objective C Ubuntu Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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%'
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Objective C Ubuntu Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks, option 3 looks good.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic