The use of LIKE in a DB Procedure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jeffrey.duffy@gro-scotland.gsi.gov.uk - 29 Aug 2005 18:42 GMT

Dear All

In a DBP I am trying to use the comparitor 'like' after declaring the
local variables as :

select col_nam1 into loc_var1
from table ;

select col_nam2 into loc_var2
from table2
where col_nam1 LIKE 'loc_var1%' ;

Despite the variable loc_var1 being properly populated the second
select statement finds no match. It just does not seem to like the '%'.
Is it something you have come across ?
Im tearing my hair out slightly.....

All help gratefully recieved chaps.......

Thanks
Jeff

Reply to this Message

Peter Gale - 29 Aug 2005 19:11 GMT

Hi Jeff,

Try this

select col_nam2 into loc_var2
from table2
where col_nam1 LIKE :loc_var1+'%'

Peter

T: +44 (0)1398 341777
PGale@Comp-Soln.co.uk

-----Original Message-----
From: info-ingres-admin@cariboulake.com
[mailto:info-ingres-admin@cariboulake.com] On Behalf Of
jeffrey.duffy@gro-scotland.gsi.gov.uk
Sent: 29 August 2005 18:43
To: info-ingres@cariboulake.com
Subject: [Info-ingres] The use of LIKE in a DB Procedure

Dear All

In a DBP I am trying to use the comparitor 'like' after declaring the
local variables as :

select col_nam1 into loc_var1
from table ;

select col_nam2 into loc_var2
from table2
where col_nam1 LIKE 'loc_var1%' ;

Despite the variable loc_var1 being properly populated the second
select statement finds no match. It just does not seem to like the '%'.
Is it something you have come across ?
Im tearing my hair out slightly.....

All help gratefully recieved chaps.......

Thanks
Jeff

_______________________________________________
Info-ingres mailing list
Info-ingres@cariboulake.com
http://mailman.cariboulake.com/mailman/listinfo.py/info-ingres

Reply to this Message

jeffrey.duffy@gro-scotland.gsi.gov.uk - 31 Aug 2005 07:25 GMT

Thanks to all who replied to my request. Unfortunately, I still cant
the blasted thing to return a results set.
I have tried variations of both Peter and Karl & Betty's suggestions
with no luck.
Here is a quick synopsis of what I tried:

Select trim(left(in_forename,1) into var1 ;
(var1 populated correctly with letter F)
var1 = var1 + '%' ;
(var1 now reads F%)

Select col1 into blah
from table
where col1 LIKE var1 ;

(This produces no results so I thought I needed quotes thus:)

quote = '''' ;
var1 = quote + var1 ;
var1 = var1 + '%' ;
var1 = var1 + quote ;

(var1 now reads 'F%' - However this also did not work)

I am still stuck !!!

Any more ideas ?

Jeff

Reply to this Message