Issue With Apostrophe - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Issue With Apostrophe

Author

Topic

fkhundmi
Starting Member

2 Posts

Posted - 2009-11-07 : 09:20:17

I have a Proc that was coded in Dynamic SQl for one of my Application. It is using to search the Applicants with their last name. Right now it is searching applicants with either their first 2 digits of their last name or full last name. But i have a problem searching Applicants that have Apostrophe in thier last name(Example O'Connor). If the client try to search applicant with O' or O'Connor it is throwing an error. They want to search every Applicant with or without Apostrophe in their last name. Please Help I tried everything, but its not working. Below is my search code that using in the Proc to pull applicants:

-- add wildcards if necessary
if Rtrim(@FirstName) <> ''
begin
If(Len(@FirstName) < 30) and (CharIndex('%', @FirstName) = 0) and @FirstName != ''
Set @FirstName = char(39) + @FirstName + '%' + char(39)
end

if Rtrim(@LastName) <> ''
begin
If(Len(@LastName) < 60) and (CharIndex('%', @LastName) = 0) and @LastName != ''
Set @LastName = Char(39) + @LastName + '%' + char(39)
end

At the end-----

--Now build dinamically the filter base on input parameters

if Rtrim(@LastName) <> ''
select @Where = @Where + ' and a.LastName like '+ Rtrim(@LastName)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-07 : 11:10:04

You mask a single quote by a second single quote.
So your solution should be to mask by using replace BEFORE yoou do anything else with that variable.
Copy and paste and run this example to see what I mean:

declare @LastName varchar(255)
set @LastName='O''Connor'
select @LastName
set @LastName=replace(@LastName,'''','''''') -- replaces one single quote by two single quotes
select @LastName

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-07 : 17:52:08

quote:
Originally posted by fkhundmi

I have a Proc that was coded in Dynamic SQl for one of my Application.

Whoah!

You got more problems the quotes

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-09 : 02:22:23

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources