How to extract all string that match a certain pattern and explode as rows in SQL

Question

I have a table like

memberid  content
1         hello @abc
2         I'm happy @sunny @rainy

My expected output

memberid  text_after_@
1         abc
2         sunny 
2         rainy

SQLFidle

Does this answer your question: https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows — VBoka, Feb 19 '20 at 06:33

score 0 · Answer 1 · answered Feb 19 '20 at 06:52

        SELECT memberid, 
          Min(Substr(content, 
         INSTR(CONTENT, 
          '@') 
        , Length(content))) over
         (Partition by Substr(content, 
         INSTR(CONTENT, 
          '@'), Length(content)) order by 
           memberid) 

        from 
        table

score 0 · Answer 2 · answered Feb 19 '20 at 11:54

This may be an ugly way of doing it, but works.

WITH RECURSIVE CTE(memberid,c1,n,cnt) AS (
SELECT MEMBERID,
       SUBSTRING(content FROM (LOCATE('@',content))+1) as c1,
       1 as n,
       LENGTH(content)-LENGTH(REPLACE(content ,'@','')) as cnt FROM TEST
union all
SELECT MEMBERID,
       SUBSTRING(c1 FROM (LOCATE('@',c1))+1),
       n+1,
       cnt FROM cte
where n<cnt
)
SELECT DISTINCT MEMBERID,
                SUBSTRING_INDEX(c1, ' ', 1) AS TEXT_AFTER 
FROM CTE 
ORDER BY MEMBERID

CHECK DEMO HERE

How to extract all string that match a certain pattern and explode as rows in SQL

2 Answers2