How to get unique values and only number after special characters

sujith kumar matharasi 351 Reputation points
2020-10-09T20:07:29.133+00:00

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('')

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,518 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,566 Reputation points
    2020-10-09T21:17:24.733+00:00

    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 |         |
    +----+---------+
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 99,071 Reputation points MVP
    2020-10-09T21:42:31.457+00:00

    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)
    
    1 person found this answer helpful.
    0 comments No comments

  2. sujith kumar matharasi 351 Reputation points
    2020-10-10T10:30:25.303+00:00

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

    0 comments No comments