Question 21708 in Mysql Question posted in
The official MySQL documentation can be found here.

How to select from database using explode – Mysql

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.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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;

  2. 0

    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

    Login or Signup to reply.
  3. 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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search