how to get a string from a list of string using SQL query

Question

I have table tableA as below:

+--------+----------------------+
|   Id   |          Ref         |
+--------+----------------------+
|    1   |    RN1-102,RN2-103   |  
|    2   |    RN1-106           |
|    3   |    RN2-203           |
|    4   |    NULL              |
|    5   |    RN1-104|,RN2-107  |
|    6   |    RN1-101,RN2-105   |
|    7   |    RN1-100,RN2-109   |
+--------+----------------------+

I need a output (distinct Ref from tableA) as below:

+--------------------+
| Distinct Ref data  |
+--------------------+
|      RN1-100       |
|      RN1-101       |
|      RN1-102       |
|      RN1-104       |
|      RN1-106       |
|      RN2-103       |
|      RN2-105       |
|      RN7-107       |
|      RN2-109       |
|      RN2-203       |
+--------------------+

I tried with below query:

select distinct Ref from tableA

Please help me out..

the schema is a bad practice I think. but try to check this out, it had the same problem: http://stackoverflow.com/questions/17308669/reverse-group-concat-in-mysql — Yohanes Khosiawan 许先汉, Oct 28 '14 at 05:00
The table u have provided is not a good practice(multiple values in one row).Try to change the schema. — theDbGuy, Oct 28 '14 at 05:32

score 1 · Accepted Answer · answered Oct 28 '14 at 05:17

Try using this

SELECT distinct Split.a.value('.', 'VARCHAR(100)') REF
           FROM   (select ID,Cast ('<M>'
                                + replace(Replace(REF, ',', '</M><M>'),'&','&amp;')
                                + '</M>' AS XML) AS Data from #Table) AS A
                  CROSS APPLY Data.nodes ('/M') AS Split(a)

score 0 · Answer 2 · edited May 23 '17 at 10:26

This question is similar to this question I answered before. I offered two approach in that answer, one is using recursive another not.

But if you already have a table stores the ref keys, you have the third choice like this:

create table ref_table (ref varchar(10), ref_name varchar(100), primary key (ref));

select
    r.ref
from 
    ref_table r
where 
    EXISTS (
        select 1 
        from tableA a
        where a.ref like '%'+r.ref+'%'  -- or ','+a.ref+',' like '%,'+r.ref+',%' 
        )

SQL FIDDLE DEMO

score 0 · Answer 3 · answered Dec 26 '14 at 11:06

SELECT distinct Split.a.value('.', 'VARCHAR(100)') REF
           FROM   (select ID,Cast ('<M>'
                                + replace(Replace(REF, ',', '</M><M>'),'&','&amp;')
                                + '</M>' AS XML) AS Data from #Table) AS A
                  CROSS APPLY Data.nodes ('/M') AS Split(a)

how to get a string from a list of string using SQL query

3 Answers3