SQL string concatenation based on field value

Question

I am wondering if there is a straightforward way of concatenating a string based on the value of a field directly in SQL. I know it would be preferable to do in an application, but in this instance, I am only able to use SQL. For example, the following table:

Labels | Qty | LabelQty | OutputString
-------+-----+----------+--------------
     1 |  30 |       30 | NULL
     2 |  60 |       30 | NULL
     2 | 120 |       60 | NULL

I would like to end up with the OutputString like so:

Labels | Qty | LabelQty | OutputString
-------+-----+----------+--------------
     1 |  30 |       30 | 30|
     2 |  60 |       30 | 30|30|
     2 | 120 |       60 | 60|60|

I know this is very easy to do in C# or VB, but I am having a hard time thinking about how to accomplish this in straight SQL. Would I need to use a cursor and do each row one at a time? Unfortunately, I can't just use the string multiplier like in Ruby, such as:

SELECT (CONVERT(VARCHAR(10), LabelQty) + '|') * Labels

Any pointers are much appreciated.

You have to tell us how you arrive at the contents of output string? Do you want the LabelQty field repeated the number of times as Labels contains with | between? — Hogan
– Hogan, Commented Feb 7, 2014 at 20:24
Correct, as the bottom SELECT indicates. Repeat the LabelQty field Labels number of times. — dub stylee
– dub stylee, Commented Feb 7, 2014 at 20:29
dub, the select at the bottom DOES NOT indicate that since * is not a valid operator for a string and this would result in a syntax error. Better to use English for this requirement. — Hogan
– Hogan, Commented Feb 7, 2014 at 20:30

Szymon · Accepted Answer · 2014-02-07 20:28:50Z

6

You can use REPLICATE() function:

select replicate(cast(LabelQty as varchar(100)) + '|', Labels)
from mytable_1

answered Feb 7, 2014 at 20:28

Szymon

43k16 gold badges99 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dub stylee Over a year ago

Wow, where has REPLICATE() been all my life! This is exactly what I was looking for, thanks!

Hogan Over a year ago

@dubstylee - REPLICATE() has been dating your twin brother.

Miller Over a year ago

1+ for too fast answering. I withdraw my answer.

AAH-Shoot · Accepted Answer · 2014-02-07 20:42:00Z

0

You can try

SELECT REPLICATE(CONVERT(VARCHAR(10), LabelQty ) + '|',Labels )

answered Feb 7, 2014 at 20:42

AAH-Shoot

6432 gold badges14 silver badges30 bronze badges

Comments

Shiva · Accepted Answer · 2014-02-07 22:03:56Z

-2

While you haven't described how to produce output string, but I think that this can be done with case expression:

http://technet.microsoft.com/en-us/library/ms181765.aspx

And sample from that article:

SELECT   ProductNumber, Name, "Price Range" = 
      CASE 
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
         WHEN ListPrice < 50 THEN 'Under $50'
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
         ELSE 'Over $1000'
      END
FROM Production.Product
ORDER BY ProductNumber ;

edited Feb 7, 2014 at 22:03

Shiva

21.1k15 gold badges88 silver badges120 bronze badges

answered Feb 7, 2014 at 20:28

vmg

10.6k15 gold badges67 silver badges91 bronze badges

2 Comments

Hogan Over a year ago

No it seems clear he wants the Label repeated as many times as the Labels column.

Thrash Bean Over a year ago

@Vitaliy this is not what he pretended, not an answer.

Collectives™ on Stack Overflow

SQL string concatenation based on field value

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related