Ignore apostrophe(') in SQL Server

  • 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

  • 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

  • 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

  • Check out QuoteName in BOL.

  • 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

  • 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

  • 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

  • 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

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

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