Post reply

Nested '' '' String Query

  • July 31, 2007 at 9:34 am

    #372992

    Hi,

    I am writing a query where I need to set a variable to a query string for example;

    SET @QueryStr = 'Select * FROM table1' this is OK and works fine, but I need to nest  additional ' ' as part of the where clause to find an empty string or null. ie

    SET @QueryStr = 'Select * FROM table1 WHERE col1 IS NULL or  col1  = ' ' '

    But I keep getting

    Unclosed quotation mark before the character string ''

     Any ideas?

     

    Thanks

     

  • July 31, 2007 at 9:50 am

    #723640

    If you run Select ''' What do you get?

    You need to double up on single quotes in SQL

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • July 31, 2007 at 9:56 am

    #723644

    I just get back;

    Msg 170, Level 15, State 1, Line 29

    Line 29: Incorrect syntax near '1211'.

    1211 being the variable and line 29 being the start of the 'Select....

  • July 31, 2007 at 9:56 am

    #723643

    SET @QueryStr = 'Select * FROM table1 WHERE col1 IS NULL or  col1  = ' + ' '

  • August 1, 2007 at 1:47 am

    #723785

    Go it.....

    '

    +''''+''''+'

    Thanks for your help!

  • August 1, 2007 at 9:28 am

    #723924

    are you saying thanks to me?

     

  • August 1, 2007 at 11:35 am

    #723966

    A procedure I use to make sure I get all the nested quotes right. First, type the string just as you want it to be:

    Select * FROM table1 WHERE col1 IS NULL or col1 = ''

    Then, wherever there is a quote mark, add another next to it.

    Select * FROM table1 WHERE col1 IS NULL or col1 = ''''

    Finally, surround the whole thing with quotes.

    'Select * FROM table1 WHERE col1 IS NULL or col1 = '''''

    There's your string.

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • August 1, 2007 at 1:14 pm

    #723986

    I tend to find that multiplying the single quotes, whilst it works perfectly well, it can make the string harder to read. To solve this I use char(39) instead:

    'SELECT * FROM table1 WHERE col1 IS NULL OR Col1 = ' + CHAR(39) + CHAR(39)

  • August 1, 2007 at 2:30 pm

    #723998

    I'm not really sure that char(39) is any easier to read than ''''. It's been a few years since I've had to break out an ASCII chart.

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

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

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