CODEWITHSUNDEEP
mysql
Woton Sampaio
Woton Sampaio

Reputation: 456

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

Upvotes: 3

Views: 222

Answers (3)

SelVazi
SelVazi

Reputation: 16043

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

Upvotes: 0

Stu
Stu

Reputation: 32619

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

Upvotes: 1

lemon
lemon

Reputation: 15482

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.

Upvotes: 2

Related Questions