problem with wildcard character search

Hi,
I have this problem in a stored procedure.
I have a field called "PartDescription" in a database table which accepts
both "_" and "%" characters, But when I write a query for searching based on
"PartDescription"
field and I enter "_" in the search field then it treats it as a wildcard
character and performs the search.
I am sending the search parameter to the sql server stored procedure. Is
there any other efficient way of escaping these wildcard characters?
Currently I am using code like this
CREATE PROCEDURE ihmtprocSearchLabCabRAIL_sp
AS
DECLARE @.v_vr_escape_char VARCHAR(1)
SET @.v_vr_escape_char = CHAR(220)
SELECT PartDescription
FROM
PartData
WHERE
PartDescription LIKE
REPLACE(REPLACE(@.proc_vr_part_desc,'_',@.
v_vr_escape_char + '_'),'%',
@.v_vr_escape_char + '%') + '%' ESCAPE @.v_vr_escape_char OR
@.proc_vr_part_desc IS NULL)Hi
Posting DDL and Example data always helps
http://www.aspfaq.com/etiquett_e.asp?id=5006
This seems fine:
CREATE TABLE PartData ( id int not null identity(1,1), PartDescription
varchar(400) )
INSERT INTO PartData ( PartDescription )
SELECT 'This is part_1'
UNION ALL SELECT 'This is part%2'
UNION ALL SELECT 'This is part 3'
SELECT * FROM PartData
CREATE PROCEDURE ihmtprocSearchLabCabRAIL_sp @.proc_vr_part_desc varchar(400)
AS
DECLARE @.v_vr_escape_char CHAR(1)
SET @.v_vr_escape_char = CHAR(220)
SELECT PartDescription
FROM PartData
WHERE @.proc_vr_part_desc IS NULL
OR PartDescription LIKE '%' +
REPLACE(REPLACE(@.proc_vr_part_desc,'_',@.
v_vr_escape_char + '_'),'%',
@.v_vr_escape_char + '%') + '%' ESCAPE @.v_vr_escape_char
EXEC ihmtprocSearchLabCabRAIL_sp @.proc_vr_part_desc = 'part'
EXEC ihmtprocSearchLabCabRAIL_sp '1'
EXEC ihmtprocSearchLabCabRAIL_sp '%'
EXEC ihmtprocSearchLabCabRAIL_sp '_'
EXEC ihmtprocSearchLabCabRAIL_sp '_1'
The only change is to add a leading '%' and varchar(1) does not make much
sense!
John
"Nishanth" <cvnishanth@.hotmail.com> wrote in message
news:%23%23%23NY5oZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have this problem in a stored procedure.
> I have a field called "PartDescription" in a database table which accepts
> both "_" and "%" characters, But when I write a query for searching based
> on
> "PartDescription"
> field and I enter "_" in the search field then it treats it as a wildcard
> character and performs the search.
> I am sending the search parameter to the sql server stored procedure. Is
> there any other efficient way of escaping these wildcard characters?
> Currently I am using code like this
> CREATE PROCEDURE ihmtprocSearchLabCabRAIL_sp
> AS
> DECLARE @.v_vr_escape_char VARCHAR(1)
> SET @.v_vr_escape_char = CHAR(220)
> SELECT PartDescription
> FROM
> PartData
> WHERE
> PartDescription LIKE
> REPLACE(REPLACE(@.proc_vr_part_desc,'_',@.
v_vr_escape_char + '_'),'%',
> @.v_vr_escape_char + '%') + '%' ESCAPE @.v_vr_escape_char OR
> @.proc_vr_part_desc IS NULL)
>