This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Wildcards in Oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Wildcards in Oracle" Watch "Wildcards in Oracle" New topic
Author

Wildcards in Oracle

Emmanuel Garrido
Greenhorn

Joined: Oct 05, 2005
Posts: 3
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

Joined: Feb 02, 2002
Posts: 1211

Hint: The string comparisons are case-sensitive i.e. ('Bank' and 'bank' are not the same)

And welcome to JavaRanch

Sonny


The future is here. It's just not evenly distributed yet. - William Gibson
Consultant @ Xebia. Sonny Gill Tweets
Emmanuel Garrido
Greenhorn

Joined: Oct 05, 2005
Posts: 3
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

Joined: Aug 20, 2001
Posts: 1817

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:



Piscis Babelis est parvus, flavus, et hiridicus, et est probabiliter insolitissima raritas in toto mundo.
Emmanuel Garrido
Greenhorn

Joined: Oct 05, 2005
Posts: 3
Thanks, that was very helpful!!

Emmanuel
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Wildcards in Oracle
 
Similar Threads
Top 10 unfortunate domain name choices
Problem in paging nation with date format
Struts and Beehive
SQL injection?
Reading data from multiple tables. Any better way than this?