Function to add character between concatenation of strings

Question

Is there a more simplistic way to add characters throughout an string at the time of concatenation? I'm thinking of utilizing an function to do this and can achieve it by doing a concatenation; however, is there an kind of alternative that can be done other than concatenation?

Here is what I need to do...There is Table A which has 8 columns, for this example col1, col2, col3, etc.

;WITH
 TABLE_A
 AS
 (
  SELECT 'ABC' AS Col1
         , 'COM' AS Col2
         , 'SMALL' AS Col3
         , '1234' AS Col4
         , 'ABC INC.' AS Col5
         , '123456789' AS Col6
         , 'ABC Of New England' AS Col7
         , 'NC123456' AS Col8
 )
  SELECT *
  FROM TABLE_A

I need to concatenate the values within the columns with an > between each. I can accomplish this by doing something along the lines of...

CONCAT(Col1,'>',Col2,'>',Col3,'>',Col4,'^',Col5,'>',Col6,'>',Col7,'>',Col8)

but I need it to be dynamic, so for example if col1 - Col3 has a value then the concatenation has to occur for only those CONCAT(Col1,'>',Col2,'>',Col3) and if Col1 - Col5 has values then CONCAT(Col1,'>',Col2,'>',Col3,'>',Col4,'^',Col5) and so on, the concatenation should only go as far as there are values.

Also to add another wrinkle, instead of an > between Col4 and Col5, I need to have a carrot ^ as the delimiter.

Is there an alternative method to do this without using CONCAT? Maybe a loop?

Answer 1

Here's an example of something that will work, although it only has minimal validation to meet your requirements.

;WITH
 TABLE_A
 AS
 (
  SELECT 'ABC' AS Col1
         , 'COM' AS Col2
         , 'SMALL' AS Col3
         , '1234' AS Col4
         , 'ABC INC.' AS Col5
         , '123456789' AS Col6
         , 'ABC Of New England' AS Col7
         , 'NC123456' AS Col8

 )
  SELECT ConcentenatedString = ISNULL(('>' + NULLIF(a.Col1,'')),'')
        +ISNULL(('>' + NULLIF(a.Col2,'')),'')
        +ISNULL(('>' + NULLIF(a.Col3,'')),'')
        +ISNULL(('>' + NULLIF(a.Col4,'')),'')
        +ISNULL(('^' + NULLIF(a.Col5,'')),'')
        +ISNULL(('>' + NULLIF(a.Col6,'')),'')
        +ISNULL(('>' + NULLIF(a.Col7,'')),'')
        +ISNULL(('>' + NULLIF(a.Col8,'')),'')
  FROM TABLE_A a

The NULLIFs are used to convert a blank string to a NULL so that the delimiter will be eliminated when concatenated to the NULL. Those NULLs are then converted back to blanks to prevent the rest of the string from being eliminated.