Camillia
asked on
Replace single quote with double when searching for name with apostrophe
I have a stored proc and I want to search for names like O'Brian. I tried this and I don't get results back
set @lastname = replace (LTRIM(RTRIM(@lastname)),' ''', '')
I think above is replacing it with blank. How can I do this?
set @lastname = replace (LTRIM(RTRIM(@lastname)),'
I think above is replacing it with blank. How can I do this?
ASKER
I've saved the name as O'Brian (with the apostrophe). When searching for the name, I cant replace the apostrophe with blank. I need to replace it with single quotes in stored proc.
This is the where clause if I replace the single quotes with blank. I need to compare it with O'Brian not OBrian
where opp.ACTIVE = 1 AND opp.lastname LIKE 'OBrian%' AND i.BusinessNameId = 6
This is the where clause if I replace the single quotes with blank. I need to compare it with O'Brian not OBrian
where opp.ACTIVE = 1 AND opp.lastname LIKE 'OBrian%' AND i.BusinessNameId = 6
Hi,
does
where opp.ACTIVE = 1 AND opp.lastname LIKE 'O''Brian%' AND i.BusinessNameId = 6
work?
Regards
David
does
where opp.ACTIVE = 1 AND opp.lastname LIKE 'O''Brian%' AND i.BusinessNameId = 6
work?
Regards
David
Hi,
Look at the quotename function
http://technet.microsoft.com/en-us/library/ms176114.aspx
eg
use ExpertsExchange
go
declare @Parameter varchar( 256 )
set @Parameter = 'O''Brien'
select @Parameter
select quotename( @parameter, '''' )
Results
----------------------
O'Brien
(1 row(s) affected)
----------------------
'O''Brien'
(1 row(s) affected)
HTH
David
Look at the quotename function
http://technet.microsoft.com/en-us/library/ms176114.aspx
eg
use ExpertsExchange
go
declare @Parameter varchar( 256 )
set @Parameter = 'O''Brien'
select @Parameter
select quotename( @parameter, '''' )
Results
----------------------
O'Brien
(1 row(s) affected)
----------------------
'O''Brien'
(1 row(s) affected)
HTH
David
ASKER
That link you have is for sql 2012. I have sql 2008
I thought maybe this should work
where opp.ACTIVE = 1 AND replace (LTRIM(RTRIM(I.LastName)), '''', '') LIKE 'OBrian%' AND i.BusinessNameId = 6
but this didn't bring any rows either.
replace (LTRIM(RTRIM(I.LastName)), '''', '') still bring O'Brian with apostrophe. That should replace it with blank so I could compare with with OBrian Correct?
I thought maybe this should work
where opp.ACTIVE = 1 AND replace (LTRIM(RTRIM(I.LastName)),
but this didn't bring any rows either.
replace (LTRIM(RTRIM(I.LastName)),
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
let me see. This cant be that hard! will post back
set @lastname = replace (LTRIM(RTRIM(@lastname)),'
What code are you using for the query/