Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72

    Unanswered: Dynamic SQL - Single Quotes in String

    I'm constructing a SQL string that needs single quotes in the WHERE clause. How do I encapsulate them in a string variable. I looked into ESCAPE and SET QUOTED_IDENTIFIER, but i don't really see any examples using string Concatenation. I'm trying to filter out the zls (0 length strings)

    This doesn't work (all single quotes):

    @sqlString = ' SELECT * FROM myTbl '
    @sqlString = @sqlString + 'WHERE fld1 <>'' '

    Thanks,
    Carl

  2. #2
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    I believe to get a sgle quote (') you need to espace it as: ''' (3 singles quotes) at least it used to work this way...

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    DECLARE @sqlString varchar(8000)

    SELECT @sqlString = ' SELECT * FROM myTbl '
    SELECT @sqlString = @sqlString + 'WHERE fld1 <> '' '''
    SELECT @sqlString
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72
    The color hint feedback that SQL Analyzer gives you helped a lot, I just kept typing quotes until the colors looked good. I found when you're in the middle of a string, 2 singles: 'SELECT ''MyVal'' FROM kludge', when you get to the ends of strings and start to work in some vars, it gets weird.

    If there is a rule, I wish I knew it, this seems more like a black art, as if those who persist will be rewarded. In most IDEs I can do it in my sleep because you have the double quotes to delimit.

    Carl

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    + ''''

    As a single quote
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by carlmal
    If there is a rule, I wish I knew it, this seems more like a black art, as if those who persist will be rewarded.
    The rule is actually pretty simple. One apostrophe begins a string, and one apostrophe ends a string. Anywhere you want a literal apostrophe within the string, put two apostrophes together.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •