How split word in rows Mysql-CodePudding

Is it possible to split a word into separate lines? All the examples I found were using something to refer to as a comma or something, but I would like to separate each letter from a word, eg:

from (my table):

id	name
1	banana

to: SELECT ...

id	letter
1	b
1	a
1	n
1	a
1	n
1	a

CodePudding user response：

One option is doing it with a recursive query, using the following two steps:

base step: get the letter in position 1
recursive step: get nth letter, using LEFT(RIGHT(1), n), which extracts the letter in position n.

Recursion is halted when the nth extracting element is higher than then length of the string.

WITH RECURSIVE cte AS (
    SELECT id, name,
           1 AS idx, 
           RIGHT(LEFT(name, 1),1) AS letter 
    FROM tab
  
    UNION ALL
  
    SELECT id, name,
           idx   1 AS idx,
           RIGHT(LEFT(name, idx 1), 1) AS letter
    FROM cte
    WHERE idx < LENGTH(name)
)
SELECT id, letter FROM cte

Output:

id	letter
1	b
1	a
1	n
1	a
1	n
1	a

Check the demo here.

CodePudding user response：

A simple way would be to join with a numbers table:

with n as (
   select * from (values row(1),row(2),row(3),row(4),row(5),row(6),row(7),row(8),row(9))x(num)
)
select t.id, Substring(name, n.num, 1)
from t
join n on n.num <= Length(t.name);

DB Fiddle

CodePudding user response：

An other way more performant is by using REGEXP_REPLACE, json_array and json_table

REGEXP_REPLACE to convert banana to b,n,a,n,a

json_array to create a json array from b,n,a,n,a

json_table will convert JSON data to tabular data.

with cte as (
  select id, REGEXP_REPLACE(name, "(.)(?!$)", "$1,") as name
   from _table
)
select cte.id, t.name
from cte
join json_table(
  replace(json_array(cte.name), ',', '","'),
  '$[*]' columns (name varchar(50) path '$')
) t;

Demo here