How to get unique values and only number after special characters

Question

Hi All,

I have a weird column from the client which has data something like this

Column 1
A_505,A_987
A_999,A_123,A_999,A_123
NULL

So, what i am trying to achieve is the below output

Column1
505,987
999,123
NULL

In the first row i should get the values after _ and in the second row as they are duplicated i should only get it once.

I have attached the sample DDL for your help, so can someone please help me on this

Create Table #Test
(
Column1 nvarchar(255)
)

Insert Into #Test Values('A_505,A_987')
Insert Into #Test Values('A_999,A_123,A_999,A_123')
Insert Into #Test Values('')

Answer 1

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, list NVARCHAR(255));
INSERT INTO @tbl (list) VALUES
('A_505,A_987'),
('A_999,A_123,A_999,A_123'),
('');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

;WITH rs AS
(
    SELECT ID
          , TRY_CAST('<root><r>' + 
                REPLACE(list, @separator, '</r><r>') + 
               '</r></root>' AS XML) AS xmldata
    FROM @tbl
)
SELECT rs.ID
    , REPLACE(rs.xmldata.query('
        for $x in distinct-values(/root/r/text())
        return fn:substring($x,3, 10)
    ').value('.','VARCHAR(255)'), SPACE(1), @separator) AS list
FROM rs;

Output

+----+---------+
| ID |  list   |
+----+---------+
|  1 | 505,987 |
|  2 | 999,123 |
|  3 |         |
+----+---------+

Answer 2

Two solutions, one with string_agg and one without. Non of them preserve the order in the original strings. To be able to that, you need a string splitter that gives you the order. See http://www.sommarskog.se/arrays-in-sql.html for an example.

; WITH numbering AS (
    SELECT Column1, row_number() OVER(ORDER BY (SELECT NULL)) AS rowno
    FROM  #Test
), splitting AS (
   SELECT DISTINCT rowno, substring(s.value, charindex('_', Column1) + 1, len(s.value)) AS str
   FROM   numbering n
   CROSS APPLY string_split(n.Column1, ',') AS s
)
SELECT string_agg(str, '.')
FROM   splitting
GROUP  BY rowno
go
; WITH numbering AS (
    SELECT Column1, row_number() OVER(ORDER BY (SELECT NULL)) AS rowno
    FROM  #Test
), splitting AS (
   SELECT DISTINCT rowno, substring(s.value, charindex('_', Column1) + 1, len(s.value)) AS str
   FROM   numbering n
   CROSS APPLY string_split(n.Column1, ',') AS s
)
SELECT substring(x.x.value('.', 'varchar(20)'), 1, len(x.x.value('.', 'varchar(20)')) -1)
FROM   numbering n
CROSS  APPLY (SELECT s.str + ','
              FROM   splitting s
              WHERE  n.rowno = s.rowno
              FOR XML PATH(''), TYPE) AS x(x)

Answer 3

Thanks a lot both of you....this helps