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: stackoverflow.com/questions/17308669/… — Yohanes Khosiawan 许先汉
– Yohanes Khosiawan 许先汉, Commented Oct 28, 2014 at 5:00
The table u have provided is not a good practice(multiple values in one row).Try to change the schema. — theDbGuy
– theDbGuy, Commented Oct 28, 2014 at 5:32

PP006 · Accepted Answer · 2014-10-28 05:17:26Z

1

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)

answered Oct 28, 2014 at 5:17

PP006

7097 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:26:00Z

0

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

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Oct 28, 2014 at 5:40

Jaugar Chang

3,2062 gold badges15 silver badges24 bronze badges

Comments

Mythily · Accepted Answer · 2014-12-26 11:06:40Z

0

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)

answered Dec 26, 2014 at 11:06

Mythily

571 gold badge4 silver badges10 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related