Concatenating to a string

Question

Concatenating to a string

DaveBriCam

SSCarpal Tunnel

Points: 4294
More actions
March 19, 2020 at 4:59 pm
Go to Answer
#3735643
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: 100950
More actions
March 19, 2020 at 5:00 pm
Answer
#3735648
```
'''' + p.firstname + ''',''' +
p.lastname + ''',''' +
i.city + ''',''' +
t.stateAbbrveation + ''',''' +
Convert(char(5), c.peopleId) + ''''
as insert_string
```
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

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

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

Phil Parkin SSC Guru Points: 246716 More actions · Answer 1

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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 2

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: 246716 More actions · Answer 3

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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ScottPletcher SSC Guru Points: 100950 More actions · Answer 4

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) A socialist is someone who will give you the shirt off *someone else's* back.

Phil Parkin SSC Guru Points: 246716 More actions · Answer 5

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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

GaryV Hall of Fame Points: 3307 More actions · Answer 6

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.