How to select from database using explode

Question

I want export data from my SQL database. Simply use :

SELECT `id`,`tags` FROM `posts`

This query give me those results :

    (1, 'handshake,ssl,windows'),
    (2, 'office,word,windows'),
    (3, 'site')

I want results in this form:

    (1, 'handshake'),
    (1, 'ssl'),
    (1, 'windows'),
    (2, 'office'),
    (2, 'word'),
    (2, 'windows'),
    (3, 'site')

How can write a query that give me this results? Thank you and sorry for my poor English.

Which DBMS are you using? Please add the correct tag: sql-server, mysql, oracle, ... — Hans Keﬆing, Dec 19 '22 at 11:04
If your are interested in separate tags, you should store the tags separately. As is, your table is not normalized; it violates the first normal form, because you store many tags in the same string. Get the database straight, rather then looking for ways to muddle through with it. — Thorsten Kettner, Dec 19 '22 at 11:15

score 2 · Answer 1 · answered Dec 19 '22 at 11:09

2

If you are using SQL Server You can apply the fuction

STRING_SPLIT

SELECT id, value
FROM posts
CROSS APPLY STRING_SPLIT(tags, ',')

Check this out: SQL Fiddle example

answered Dec 19 '22 at 11:09

Dúver Cruz

328
1
12

thank you. this work on SQL Server , but i use mysql. – PCcloob.Ir Dec 19 '22 at 17:45
okay maybe this can helps https://stackoverflow.com/questions/34992575/mysql-substring-extraction-using-delimiter – Dúver Cruz Dec 20 '22 at 09:01

score 0 · Accepted Answer · answered Dec 20 '22 at 10:09

After many search and try finally i find the solution:

 SELECT
   DISTINCT postid , SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', n.digit+1), ',', -1) val
FROM
  posts
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
  ON LENGTH(REPLACE(tags, ',' , '')) <= LENGTH(tags)-n.digit;

imiezmistral · Answer 3 · 2022-12-22T06:43:24.153

0

For a max of three words, the code below can be used. If you want more words then you just add more lines. The method may not be fully automated, but it works.

SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', 1), ',', -1) FROM tabela 
            UNION
    SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', 2), ',', -1) FROM tabela 
            UNION
    SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', 3), ',', -1) FROM tabela
    ORDER BY id;

edited Dec 22 '22 at 06:43

answered Dec 20 '22 at 11:42

imiezmistral

44
6

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 21 '22 at 23:09

How to select from database using explode

3 Answers3