Concatenating to a string

  • DaveBriCam

    SSCarpal Tunnel

    Points: 4022

    I am trying to concatenate values into one string value and what i have so far is progress, but I want to encapsulate my variables with single quotes. The below gives me e.g. Karen,Cox,Oakland,MN,95573 when what I need is 'Karen','Cox','Oakland','MN','95573':

    p.firstname + ',' + 
    p.lastname + ',' +
    i.city + ',' +
    t.stateAbbrveation + ',' +
    Convert(char(5), c.peopleId)
    as insert_string
  • ScottPletcher

    SSC Guru

    Points: 98434

     

    '''' + p.firstname + ''',''' +
    p.lastname + ''',''' +
    i.city + ''',''' +
    t.stateAbbrveation + ''',''' +
    Convert(char(5), c.peopleId) + ''''
    as insert_string

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Phil Parkin

    SSC Guru

    Points: 244449

    You may be interested in using the CONCAT() function – it's tidier (IMO) than all those plus signs, does datatype conversion and handles NULLs for you.

    Also note that you have misspelled abbreviation.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719198

    Look at ConCat(), but what Scott did was escape out the single quotes with extras. That's a common technique for adding them to strings.

  • Phil Parkin

    SSC Guru

    Points: 244449

    I'd make it less cluttered by using a variable for the literal single quote:

    DECLARE @H CHAR(1) = '''';

    the CONCAT version then becomes something like this:

          CONCAT(
    @H
    ,p.firstname
    ,@H
    ,','
    ,@H
    ,p.lastname
    ,@H
    ,','
    ,@H
    ,i.city
    ,@H
    ,','
    ,@H
    ,t.stateAbbrveation
    ,@H
    ,','
    ,@H
    ,CONVERT(CHAR(5), c.peopleId)
    ,@H
    );

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • ScottPletcher

    SSC Guru

    Points: 98434

    Phil Parkin wrote:

    I'd make it less cluttered by using a variable for the literal single quote:

    DECLARE @H CHAR(1) = '''';

    the CONCAT version then becomes something like this:

          CONCAT(
    @H
    ,p.firstname
    ,@H
    ,','
    ,@H
    ,p.lastname
    ,@H
    ,','
    ,@H
    ,i.city
    ,@H
    ,','
    ,@H
    ,t.stateAbbrveation
    ,@H
    ,','
    ,@H
    ,CONVERT(CHAR(5), c.peopleId)
    ,@H
    );

    I wouldn't.  On principle I never use a single-char name, since it's by definition meaningless.  I might create, say, @quote and @quotes_with_comma.  Btw, w.t.heck does H stand for in that "name" anyway??

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Phil Parkin

    SSC Guru

    Points: 244449

    Btw, w.t.heck does H stand for in that "name" anyway??

    That is my mistake! For some reason, I had the word 'hyphen' in my mind when I wrote the code, and that's where it came from.

    In this instance, I used the single letter to reduce the real estate/clutter created by masses of variable repeats.

    I always use meaningful variable names, unless, as here, there is reason not to.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • gvoshol 73146

    Hall of Fame

    Points: 3175

    Phil Parkin wrote:

    Also note that you have misspelled abbreviation.

    That may not be the OP's fault.  It might be inherited from whenever the tables were created.

    I've run across it many times in my work.  (I vary from excusing it because someone might have English as a second language with grumbling because someone obviously wasn't paying attention.)

    Often it's easier to repeat the error than to try to find and correct every place the misspelled field name might be used.

     

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

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