select LastName = O'Connor but got error

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)

select LastName = O'Connor but got error

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2005-10-10 : 13:47:59

How to handle last name like O'Connor, D'Avino?
If I use:

Select * from tblPeople where LastName = O'Connor

I got error.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-10 : 14:08:54

You need to double up the single quote:
= 'O''Connor'

the REPLACE function is a handy way to deal with this

Be One with the Optimizer
TG

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2005-10-10 : 15:20:23

But, I use parameter like:

Select * from tblPeople where LastName = ' & strLName & '

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-10 : 17:00:16

One of the many reasons to use stored procedures rather than builing in-line sql from your application.

However, doesn't VB have a string REPLACE function? Something like:
"...where LastName = '" & strLName.replace("'", "''") & "'"

Be One with the Optimizer
TG

tribune
Posting Yak Master

105 Posts

Posted - 2005-10-11 : 01:24:44

What TG said will work.

select * from users where firstname = 'O''connar'

What you want to do is a write a small function using the replace function to pad those quotes. This will also help protect against SQL injection.

E.g.


Function PadQuotes(ByVal input as string) as string
     return Replace(input, "'", "''")
End Function

--------------------
"dell never told me i'd get free fried nut sandwiches with my laptop"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources