This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes trim on where clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "trim on where clause" Watch "trim on where clause" New topic
Author

trim on where clause

Jay Ram
Ranch Hand

Joined: Dec 13, 2005
Posts: 40
Hi,
I am trying to write a sql to get an acct_no from a table say account

I have a table account and acct_no is '123 ' (with spaces)
the acct_no table is defined to be of length 8 and whenever i insert a value, it inserts with spaces.

now when i do
select * from account where acct_no='123';

i get the row.

but when i do
select * from account where acct_no like '123';

i get no row as there are spaces.

i need to do 'like' as acct_no is an optional criteria and i am using a variable that has value sometimes and when no value i do %

As a solution, if i try
select * from account where acct_no like '123%' it works.. but the problem is it gets the row where acct_no = 1234 also.

How do i trim the table value and check..

Pls advise.

Thanks,
Jay.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2543
    
  10

i need to do 'like' as acct_no is an optional criteria and i am using a variable that has value sometimes and when no value i do %
Drop that part of the where clause when no value is given. Makes the solution cleaner.

Regards, Jan


OCUP UML fundamental and ITIL foundation
Jay Ram
Ranch Hand

Joined: Dec 13, 2005
Posts: 40
Thanks for the quick response.
The problem is I am using sqlj and i have 6 optional criterias like this.

So, in that case, i'll have to multiple sqls.

Thats the reason I was asking..

-Jay.
Jay Ram
Ranch Hand

Joined: Dec 13, 2005
Posts: 40
select * from account where ltrim(rtrim(acct_no)) like '123'

works!!

Thank you all who took time to look at this.

-Jay.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2543
    
  10

Jay,
You'll pay a price for this. Your database will stop using indexes.

Regards, Jan
dennis zined
Ranch Hand

Joined: Mar 07, 2003
Posts: 330
Originally posted by Jan Cumps:
Jay,
You'll pay a price for this. Your database will stop using indexes.

Regards, Jan


+1


SCJP 1.4<br />SCWCD 1.4
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 32481
    
214

Originally posted by Jan Cumps:
Your database will stop using indexes.

Some databases, like Oracle, have function based indexes.


[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
dennis zined
Ranch Hand

Joined: Mar 07, 2003
Posts: 330
Originally posted by Jeanne Boyarsky:

Some databases, like Oracle, have function based indexes.


dang!...learned something today.
Jay Ram
Ranch Hand

Joined: Dec 13, 2005
Posts: 40
I am using db2. Will my sql affect db2?

if so, whats the alternative?

Pls advise.

Thanks,
Jay.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: trim on where clause
 
jQuery in Action, 3rd edition