Post reply

Inserting apostrophes into result set

  • August 22, 2017 at 10:05 am

    #345880

    Hi all,

    Been having some fun with the following code, and hit a roadblock.  It generates an update statement (to be used Live to Test)

    Select 'Update mick_settings Set setting_value = ' + (SETTING_VALUE) + ' Where SETTING_NAME Like ''%URL%'
    From Mick_settings

    But as you can see, the resulting Update statement lacks apostrophes around the URL, as well as the very end;

    Update mck_settings Set setting_value = http://WWW.OurUrl/ Where SETTING_NAME Like '%URL%

    Whereas the desired result is

    Update mck_settings Set setting_value = 'http://WWW.OurUrl/' Where SETTING_NAME Like '%URL%'

    Thanks,

    JB

  • August 22, 2017 at 10:36 am

    #1956204

    You're already most of the way there, you just need to double up the quotes in a few places:
    Select 'Update mick_settings Set setting_value = ''' + (SETTING_VALUE) + ''' Where SETTING_NAME Like ''%URL%'''
    From Mick_settings
    The above should do the trick.

  • August 22, 2017 at 12:16 pm

    #1956230

    I would use QUOTENAME instead, because it will handle input data that contains apostrophe's.
    Select 'Update mick_settings Set setting_value = ' + QUOTENAME(SETTING_VALUE, '''') + ' Where SETTING_NAME Like ''%URL%'
    From Mick_settings

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 3 posts - 1 through 2 (of 2 total)

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