Re: Like clause in store procedure

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1079715860.379852_at_yasure...
| Jawahar Rajan wrote:
| > All,
| > I have a stored procedure that return a Ref Cursor
| > The SQL in the procedure is
| >
| > Procedure getPatName(in_name IN Varchar2, retCursor OUT patCursor)
| > Is
| > Begin
| > Open retCursor For
| > SELECT NAME, weight
| > FROM patient
| > WHERE patient_name like = in_name;
| >
| > End getPatName;
| >
| > I have defined patCursor in the spec of the package of which getPatName
is a
| > part of
| >
| > This procedure does return any rows despite the data being present I am
| > assuming the SQL using like is not correct. How can I build a SQL in a
| > procedure using LIKE - is the above correct?
| >
| > Jawahar
|
| SELECT ...
| FROM ...
| WHERE patient_name LIKE '%' || in_name || '%';
|
| Take a look at the Soundex function too:
| http://www.psoug.org/reference/string_func.html
|
| --
| Daniel Morgan
| http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
| http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
| damorgan_at_x.washington.edu
| (replace 'x' with a 'u' to reply)
|

ouch! avoid the leading '%'!

code the procedure with

WHERE patient_name like in_name || '%'

and let the user (or interface) supply the leading '%' in the (hopefully) rare case in which it's needed

;-{ mcs Received on Fri Mar 19 2004 - 19:44:12 CST