SQL handling single and double quotes

Hi there,
I have to insert and delete some data using names that may contain single or double quotes.


INSERT INTO Table (Name,Value) VALUES (' "nameString" ', ' "valueString" ');
DELETE FROM Table WHERE Name= "nameString";

When nameString has a single Quote like, a'mbuli, then I replace the single quote with two single quotes like (a''mbuli) and it works.

But, how can I handle a double quote for example, a"mbuli

Thank you.
ambuliAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Always use only single quotes to enclose a string literal. Double quotes have a different meaning in ANSI SQL. Inside of single quotes, you only need to care about literal single quotes (which have to be doubled), everything else including double quotes just works.
0
Ryan ChongCommented:
Qlemo is correct for the explanation. so for your example, you should do this:
INSERT INTO Table (Name,Value) VALUES ( 'nameString' , 'valueString' );
DELETE FROM Table WHERE Name= 'nameString' ;

Open in new window


if the string value itself contains a single quote, like a'mbuli,  you can try:
INSERT INTO Table (Name,Value) VALUES ( 'nameString' , 'a''mbuli' );
DELETE FROM Table WHERE Value = 'a''mbuli' ;

Open in new window

0
PortletPaulfreelancerCommented:
You have identified the question with MySQL as a topic - this is important because that product has some specific information about quoted literals.

In MySQL you can (if allowed by settings) use either single quote [`] or double quote ["] to quote literals.

HOWEVER: If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only within single quotation marks

So: It is STRONGLY RECOMMEND you ALWAYS use ONLY the single quote [`] for quoted literals.

If you follow that advice only an embedded single quote [`] needs special handling which you can do by repeating the embedded single quote OR you can use a backslash in front of the single quote.

Here are some examples
    CREATE TABLE AnyTable
        (`StringColumnA` varchar(60), `StringColumnB` varchar(60))
    ;
        
    INSERT INTO AnyTable
        (`StringColumnA`, `StringColumnB`)
    VALUES
        ('NameString', 'Uncomplicated string'),
        ('"NameString"', '"also not complicated"'),
        ('`bacticked`', 'Embedded`Bactick is not a problem'),
        ('Embedded"Double', 'Double"Embedded is not an issue either'),
        ('Embedded''Single', 'Single''Embedded has a need'), /* these need escaping */
        ('Embedded\'Single', 'Single\'Embedded has a need')  /* these need escaping */
    ;
    
    

Open in new window

    select 1 as Num, `StringColumnA`, `StringColumnB` from AnyTable
    where `StringColumnA` = '"NameString"' 


    | Num | StringColumnA |          StringColumnB |
    |-----|---------------|------------------------|
    |   1 |  "NameString" | "also not complicated" |

Open in new window

   
    select 2 as Num, `StringColumnA`, `StringColumnB` from AnyTable
    where `StringColumnA` LIKE '%"%' 


    | Num |   StringColumnA |                          StringColumnB |
    |-----|-----------------|----------------------------------------|
    |   2 |    "NameString" |                 "also not complicated" |
    |   2 | Embedded"Double | Double"Embedded is not an issue either |

Open in new window

    
    /* repeat the embedded single */
    select 3 as Num, `StringColumnA`, `StringColumnB` from AnyTable
    where `StringColumnA` = 'Embedded''Single' 


    | Num |   StringColumnA |              StringColumnB |
    |-----|-----------------|----------------------------|
    |   3 | Embedded'Single | Single'Embedded has a need |
    |   3 | Embedded'Single | Single'Embedded has a need |

Open in new window

 
    /* use an escape */
    select 4 as Num, `StringColumnA`, `StringColumnB` from AnyTable
    where `StringColumnA` = 'Embedded\'Single' 


    | Num |   StringColumnA |              StringColumnB |
    |-----|-----------------|----------------------------|
    |   4 | Embedded'Single | Single'Embedded has a need |
    |   4 | Embedded'Single | Single'Embedded has a need |

Open in new window

    /* repeat the embedded single */
    select 5 as Num, `StringColumnA`, `StringColumnB` from AnyTable
    where `StringColumnA` LIKE '%''%' 


    | Num |   StringColumnA |              StringColumnB |
    |-----|-----------------|----------------------------|
    |   5 | Embedded'Single | Single'Embedded has a need |
    |   5 | Embedded'Single | Single'Embedded has a need |

Open in new window

    /*  use an escape */
    select 6 as Num, `StringColumnA`, `StringColumnB` from AnyTable
    where `StringColumnA` LIKE '%\'%' 


    | Num |   StringColumnA |              StringColumnB |
    |-----|-----------------|----------------------------|
    |   6 | Embedded'Single | Single'Embedded has a need |
    |   6 | Embedded'Single | Single'Embedded has a need |

Open in new window


See these working at: http://sqlfiddle.com/#!9/f11b2/2
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.