Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Wildcards in Oracle

 
Emmanuel Garrido
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello there!

I've been trying to use a wildcard in my SQL statement where a string value of "bank" should find all elements in a database field including that word, i.e.

National Bank
First Bank
Us Bank

I've tried to do the following:

select * from branch where branchname like '%bank%';--Returns no rows
select * from branch where branchname like '%bank'; --Returns no rows
select * from branch where branchname like 'bank%'; --Returns no rows
select * from branch where branchname like 'bank_'; --Returns no rows
select * from branch where branchname like '_bank_';--Returns no rows
select * from branch where branchname like '_bank';--Returns no rows


I've checked the current values in the database and they are as follows:

SQL> select branchname from branch;

BRANCHNAME
------------------------------------
US Bank - Idaho Falls
US Bank - Rexburg
US Bank - Rigby
Beehive Credit Union - Provo
Beehive Credit Union - Rexburg

Anybody has any suggestions? I will really appreciatte your help
[ October 05, 2005: Message edited by: Bear Bibeault ]
 
Sonny Gill
Ranch Hand
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hint: The string comparisons are case-sensitive i.e. ('Bank' and 'bank' are not the same)

And welcome to JavaRanch

Sonny
 
Emmanuel Garrido
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wow, I never realized that it was case sensitive for the wildcards, I just asumed that since SQL is not case-sensitive (most of the times as I have learned today) I didn't need to specify that

Thank you, it worked just fine.

Emmanuel
 
Joel McNary
Bartender
Posts: 1840
Eclipse IDE Java Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Whether or not SQL is case sensitive depends on the database and how the database is configured. Oracle can be either, as can SQL Server.

To get case insensitive searches, use the UPPER (or LOWER) function:

 
Emmanuel Garrido
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, that was very helpful!!

Emmanuel
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic