File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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


Win a copy of Head First Android this week in the Android forum!
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: 2541
    
  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: 2541
    
  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: 32319
    
213

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]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, 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
 
It's not a secret anymore!