How to select one char of a string as individual row MySQL

Question

In MySQL, how would I select each character in a given string as individual rows, if the length of the string is unknown. For example, if I have the string:

SET @str:='abc';

I would like a SELECT statement to produce:

rownum  char
======  ====
1       a
2       b
3       c

If possible, I would like to avoid temp tables or stored procedures.

Look at this answer https://stackoverflow.com/questions/14959166/mysql-select-query-get-only-first-10-characters-of-a-value @user1822391 Or Follow this link https://www.w3resource.com/mysql/string-functions/mysql-substring-function.php — Raj Kumar, Mar 06 '20 at 17:13
It could be done somewhat easily if there's an upper bound to the string length. — Nae, Mar 06 '20 at 17:14
@Nae, Let assume the upper bound will be 10... what would you suggest to accomplish this? — user1822391, Mar 06 '20 at 17:24
@Raj Kumar This question is not related to my question... I am looking to have each char of a string selected as individual rows, not select the first X chars from a string. — user1822391, Mar 06 '20 at 17:27

Nae · Accepted Answer · 2020-03-06T18:33:19.180

2

SELECT @var_i + 1 AS rownum, SUBSTR(@var_str, (@var_i := @var_i + 1), 1) AS `char`
FROM (SELECT (@var_str := "abc") AS _, (@var_i := 0) AS __) AS pow0
CROSS JOIN (SELECT 0 AS _ UNION ALL SELECT 0) AS pow1
CROSS JOIN (SELECT 0 AS _ UNION ALL SELECT 0) AS pow2
CROSS JOIN (SELECT 0 AS _ UNION ALL SELECT 0) AS pow3
CROSS JOIN (SELECT 0 AS _ UNION ALL SELECT 0) AS pow4 -- 8 < 10 < 16
WHERE @var_i < CHAR_LENGTH(@var_str)
;

edited Mar 06 '20 at 18:33

answered Mar 06 '20 at 17:54

Nae

14,209
7
52
79

1

I like your idea... if I know the upper bound & do not want to use stored procedure & temp tables. I modified your SQL statement to limit to not show the empty rows with ```WHERE @var_i < CHAR_LENGTH(@var_str)```. – user1822391 Mar 06 '20 at 18:30

score 1 · Answer 2 · answered Mar 06 '20 at 17:32

With this procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_splitstring`(IN _s TEXT)
BEGIN
DECLARE _i INTEGER;
DECLARE _n INTEGER;
DROP TABLE IF EXISTS listchar;
CREATE TEMPORARY TABLE listchar ( singlechar varchar(1));
SELECT LENGTH(_s) INTO _i;
SET _n = 0;
    loop_label:  LOOP
        IF  _n > _i THEN 
            LEAVE  loop_label;
        END  IF;

        SET  _n = _n + 1;
        INSERT INTO listchar SELECT SUBSTRING(_s,_n,1);
    END LOOP;
SELECT * FROM listchar;
DROP TABLE listchar;
END

Result:

How to select one char of a string as individual row MySQL

2 Answers2