aspose file tools*
The moose likes JDBC and the fly likes Case insensitive like in postgresql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Case insensitive like in postgresql" Watch "Case insensitive like in postgresql" New topic
Author

Case insensitive like in postgresql

D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

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?


Retire your iPod and start with HD Android music player Kamerton | Minimal J2EE container is here | Light weight full J2EE stack | and build tool | Co-author of "Windows programming in Turbo Pascal"
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30294
    
150

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%'


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2495
    
    8

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


OCUP UML fundamental and ITIL foundation
youtube channel
D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

Thanks, option 3 looks good.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Case insensitive like in postgresql