SQL - combine 2 string lists

Question

I have something like this:

DECLARE @a VARSCHAR(100); SET @a = 'a,b,c,d,e'
DECLARE @b VARSCHAR(100); SET @b = '1,2,3,4,5'

I need something like this:

@c = 'a1,b2,c3,d4,e5'

The a and b have always the same length.

I was thinking of doing a split by comma, into temp tables, and then parse them with a do while loop. I was wondering though, maybe there is something more elegant than that?

Don't store lists in strings. That is the wrong way to store values. If you are declaring variables, learn about table variables. — Gordon Linoff, Mar 13 '17 at 13:06
@GordonLinoff I get this sometimes on stackoverflow. People giving me "better ideas". I am sure that sometimes when you deal with legacy code or something like that, you don't really have a choice, you have to work with what you got. I had a lot of great ideas, and the client's response was 'we have no money/time to do that right now'. I cannot change a and b data types this time. — Ash, Mar 13 '17 at 13:11
*'I get this sometimes on stackoverflow. People giving me "better ideas".'* - Are you suggesting that someone giving you a better idea is a problem with Stackoverflow? — GarethD, Mar 13 '17 at 13:44
@GarethD . Ok my wording is not perfect, or it was sarcasm on your side :-). Basically, if you have an assignment, to make omelette, and you ask for help, then you don't want to hear that pancake is better ... — Ash, Mar 13 '17 at 13:54
There was a hint of sarcasm. You need to make things like this clear in your question, for every one person that can't make changes, there is probably one that would benefit from Gordon's advice. This person goes on to make the changes suggested and their whole database is improved as a result. So, if you find these suggestions annoying, then you need to make it clear that you know this is the ideal solution, but can't make any changes to the data type. This should keep the answers relevant. — GarethD, Mar 13 '17 at 14:06

score 2 · Accepted Answer · answered Mar 13 '17 at 13:16

using the stuff() with select ... for xml path ('') method of string concatenation and a CSV Splitter table valued function by Jeff Moden we can split, join, and concatenate like so:

select stuff((
    select ','+a.Item+b.Item
    from [dbo].[delimitedsplit8K](@a,',') a
      inner join dbo.[delimitedsplit8K](@b,',') b
        on a.ItemNumber = b.ItemNumber
      for xml path (''), type).value('.','nvarchar(max)')
  ,1,1,'')

rextester demo:http://rextester.com/EAAMHG75908

returns: a1,b2,c3,d4,e5

splitting strings reference:

SQL - combine 2 string lists

1 Answers1