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

How split word in rows Mysql

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
Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search