Ignore apostrophe(') in SQL Server

Question

Ignore apostrophe(') in SQL Server

VRT

SSCertifiable

Points: 5776
More actions
February 9, 2009 at 11:27 am

#127944

Hi,
How can we ignore the apostrophe (') in one of the column.
I have a select statement which needs to retrieve values based on emp name. But some of the employee names have Apostrophe('). Due to this, the query parse error is coming.
select * from Employee where empname='Venkat R'Thota'
Here the query is endind with first apostrophe(') and giving parse error.
How can we fix it up in SQL Server?
Thanks in advance,
Venki.
Thank You

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

If you put two apostrophes in a row in that, SQL will allow it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

VRT SSCertifiable Points: 5776 More actions · Answer 2

GSquared (2/9/2009)
If you put two apostrophes in a row in that, SQL will allow it.

Thanks for your response.

But my question is I want to do it in single stmt.

Actually, I have to take this name into another variable and need to do it.

I want to do it in the same select stmt with out taking the variables and all.

Is it possible?

Thank You

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Check out QuoteName in BOL.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 4

Are you building the statement in, for example, a web page?

If so, you need to do something like

Name = "Venkat R'Thota"

SQL = "select * from Employee where empname='" & replace(Name,"'","''")

The end result is

select * from Employee where empname='Venkat R''Thota'

Derek

GSquared SSC Guru Points: 260824 More actions · Answer 5

venki (2/9/2009)
GSquared (2/9/2009)
If you put two apostrophes in a row in that, SQL will allow it.
Thanks for your response.
But my question is I want to do it in single stmt.
Actually, I have to take this name into another variable and need to do it.
I want to do it in the same select stmt with out taking the variables and all.
Is it possible?

You'll either need to manipulate the string, or use a variable/parameter. Those are the only answers that will work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

VRT SSCertifiable Points: 5776 More actions · Answer 6

I am doing a vb program which requires this one.

I will first taking that employee name into a variable and checking for apostrophe(') and if it is there then replace with '(single) with ''(double) apostrophes.

I thought there will be a function or a way to take that Apostrophe(') as a string.

Thanks a lot for your suggestions.

Thank You

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 7

venki (2/11/2009)
I am doing a vb program which requires this one.
I will first taking that employee name into a variable and checking for apostrophe(') and if it is there then replace with '(single) with ''(double) apostrophes.

You don't need to bother with checking. If you just doname=replace(name,"'","''")It will double up any single quotes but do nothing if there are none.

Also you don't want double quotes (") you want repeated single quotes(''). Check what's in the code above.

Derek