mysql How to select from database using explode

gr8qqesn 于 2022-12-22 发布在 Mysql

关注(0)|答案(3)|浏览(147)

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.

mysql

来源：https://stackoverflow.com/questions/74849529/how-to-select-from-database-using-explode

3条答案

按热度按时间

kzipqqlq1#

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

赞(0）回复(0）举报 2022-12-22

lzfw57am2#

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;

赞(0）回复(0）举报 2022-12-22

c9qzyr3d3#

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;

赞(0）回复(0）举报 2022-12-22

我来回答

mysql How to select from database using explode

3条答案

相关问题

热门标签

最新问答