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?
