• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

trim on where clause

 
Jay Ram
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2602
13
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Jay Ram
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2602
13
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jay,
You'll pay a price for this. Your database will stop using indexes.

Regards, Jan
 
dennis zined
Ranch Hand
Posts: 330
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jan Cumps:
Jay,
You'll pay a price for this. Your database will stop using indexes.

Regards, Jan


+1
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34965
377
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jan Cumps:
Your database will stop using indexes.

Some databases, like Oracle, have function based indexes.
 
dennis zined
Ranch Hand
Posts: 330
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jeanne Boyarsky:

Some databases, like Oracle, have function based indexes.


dang!...learned something today.
 
Jay Ram
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using db2. Will my sql affect db2?

if so, whats the alternative?

Pls advise.

Thanks,
Jay.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic