Hi folks,
I'm building a Data Access Layer that connects to an Oracle 9i db. Most of the SELECT queries with = parameters work fine, for example:
SELECT * EMAIL_ADDRESS, EMPLOYEE_NUMBER WHERE SURNAME = :sname
However, I need to carry out a filtered search using the LIKE clause and can't get it to work. Here is what I've for:
SELECT EMAIL_ADDRESS, EMPLOYEE_NUMBER, FIRST_FORENAME FROM [table name] WHERE (SURNAME LIKE '%:sname%')
This does not work, as the paramter gets lost within the '' and the value is treated purely as %:sname%
I've looked around the internet for clues and as yet have found nothing on the correct sytnax. Surely I'm not the first person to have to carry out a LIKE search on an Oracle db using a data set?
Can anyone help? Is there an alternative method that I can use?
Just curious. Is there any reason why you wouldn't use Oracle Packages/Stored Procedures to retrieve data? I don't know specifically what is wrong with doing it as you have I've just always used Pkgs/Stored Procs.
Member
1 Points
16 Posts
Dataset, Oracle & Filter
Jul 07, 2010 07:15 AM|AlanRuth|LINK
Hi folks,
I'm building a Data Access Layer that connects to an Oracle 9i db. Most of the SELECT queries with = parameters work fine, for example:
SELECT * EMAIL_ADDRESS, EMPLOYEE_NUMBER WHERE SURNAME = :sname
However, I need to carry out a filtered search using the LIKE clause and can't get it to work. Here is what I've for:
SELECT EMAIL_ADDRESS, EMPLOYEE_NUMBER, FIRST_FORENAME FROM [table name] WHERE (SURNAME LIKE '%:sname%')
This does not work, as the paramter gets lost within the '' and the value is treated purely as %:sname%
I've looked around the internet for clues and as yet have found nothing on the correct sytnax. Surely I'm not the first person to have to carry out a LIKE search on an Oracle db using a data set?
Can anyone help? Is there an alternative method that I can use?
Many Thanks,
Alan R
dataset
Participant
1043 Points
295 Posts
Re: Dataset, Oracle & Filter
Jul 07, 2010 09:33 AM|nomercy007|LINK
you have to concatenate your parameter with the %
WHERE (SURNAME LIKE '%' || :sname || '%')
regards,
Member
1 Points
16 Posts
Re: Dataset, Oracle & Filter
Jul 07, 2010 09:36 AM|AlanRuth|LINK
Fantastic Ali, That works perfectly.
Many thanks.
Member
35 Points
52 Posts
Re: Dataset, Oracle & Filter
Jul 07, 2010 02:12 PM|aspnoob07|LINK
Just curious. Is there any reason why you wouldn't use Oracle Packages/Stored Procedures to retrieve data? I don't know specifically what is wrong with doing it as you have I've just always used Pkgs/Stored Procs.