How to split the name string in mysql?

Question

How to split the name string in mysql ?

E.g.:

name
-----
Sachin ramesh tendulkar
Rahul dravid

Split the name like firstname,middlename,lastname:

firstname   middlename    lastname
---------  ------------   ------------
sachin     ramesh         tendulkar
rahul      dravid

when your tables are properly normalize, you don;t have any problem on this. *You don't need to split the values.* — John Woo, Feb 19 '13 at 05:31
possible duplicate of [Can you split/explode a field in a MySQL query?](http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query) — Matt Ball, Feb 19 '13 at 05:31
@JW. unfortunately, names in particular are a tough one. http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ — Matt Ball, Feb 19 '13 at 05:37
@Madhav If any of the answers solved your question click solved... hint hint ;) — Jesse C, Jan 21 '15 at 19:46
Trying to figure out parts of a name as being "first" or "last" name is a path to sorrow. Not all names are two "words" separated by a space. Consider Eddie Van Halen (2 words in last name) or Zack de la Rocha (3 words in last name) or Jerry Lee Lewis (2 words in first name). Don't forget hyphenates like Olivia Newton-John and Yo-Yo Ma or apostrophes like Sinead O'Connor. You are not going to have a consistently accurate way to do this task based on patterns. — Andy Lester, Feb 14 '21 at 18:53
@John Woo: tell that to whoever designed `information_schema.user_privileges`. It wasn't me! — reinierpost, May 18 '22 at 10:47
Fundamentally this is a schema failure. First/last names should be kept in different fields. You can even add middle name for good measure. That way, it's up to the user to put their names in the right fields, and your logic suddenly gets a lot simpler. — William T Froggard, Jan 17 '23 at 21:46

score 248 · Answer 1 · edited Dec 30 '20 at 15:21

248

I've separated this answer into two(2) methods. The first method will separate your fullname field into first, middle, and last names. The middle name will show as NULL if there is no middle name.

SELECT
   SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 1), ' ', -1) AS first_name,
   If(  length(fullname) - length(replace(fullname, ' ', ''))>1,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 2), ' ', -1) ,NULL) 
           as middle_name,
   SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 3), ' ', -1) AS last_name
FROM registeredusers

This second method considers the middle name as part of the lastname. We will only select a firstname and lastname column from your fullname field.

SELECT
   SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 1), ' ', -1) AS first_name,
    TRIM( SUBSTR(fullname, LOCATE(' ', fullname)) ) AS last_name
FROM registeredusers

There's a bunch of cool things you can do with substr, locate, substring_index, etc. Check the manual for some real confusion. http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

edited Dec 30 '20 at 15:21

danronmoon

3,814
5
34
56

answered Feb 19 '13 at 06:26

Jesse C

2,800
1
14
6

10

Gosh that intel has just made me feel super human with mysql pro skills. I have ave completely removed PHP processing and turned 100 lines of logic in a one single query. This is amazing! – TeaCupApp Feb 04 '14 at 03:35
2

Really nice. Did exactly what i needed and probably solved the askers problem event better than he wished, since in case of two words only firstname and lastname is set not firstname and middlename. – Jānis Gruzis Apr 01 '14 at 08:18
The first method helped me a lot. Only issue I saw was a last name "St. George" got picked up as middle name "St.". – Joe M. Aug 19 '15 at 20:42
Or you could do the same with 5 minutes of php scripting. I mean, this is an awesome solution, but seems way too complex when compared to a few minutes with a cli or script. – Captain Hypertext Oct 29 '15 at 14:24
2

Hello sir I know this is an old thread. How about if the person has an extension name? – Trafalgar D Law Oct 06 '16 at 07:42
It didn't work with names with 4 or more words like "ABDOULAYE DIT GOUMB SOW" – Gildonei Jun 28 '17 at 12:23
2

@TrafalgarDLaw I posted a solution above. - https://stackoverflow.com/a/44802256/3542883 – Gildonei Jun 28 '17 at 12:25
For the SQL rookies - this won't actually update your columns, for that you'll need to run this inside an `UPDATE` operation – cs_stackX Aug 30 '17 at 21:29

score 59 · Answer 2 · edited May 22 '18 at 14:50

59

There is no string split function in MySQL. so you have to create your own function. This will help you. More details at this link.

Function:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

Usage:

SELECT SPLIT_STR(string, delimiter, position)

Example:

SELECT SPLIT_STR('a|bb|ccc|dd', '|', 3) as third;

+-------+
| third |
+-------+
| ccc   |
+-------+

edited May 22 '18 at 14:50

Giacomo1968

25,759
11
71
103

answered Feb 19 '13 at 05:35

Rahul Chipad

2,373
17
20

2

This is the most concise solution by far; a thing of beauty! I had to add 'DETERMINISTIC' clause after the 'RETURNS' to get it to run, due to my local DB settings. – Vince Hill Mar 05 '22 at 04:57
@VinceHill I believe I have the same settings. But having trouble finding exactly where to place 'DETERMINISTIC' to avoid the error message. – Robert Koernke May 17 '23 at 21:18
CREATE FUNCTION COMMON.SPLIT_STR( x VARCHAR(255), delim VARCHAR(12), pos INT ) RETURNS VARCHAR(255) DETERMINISTIC RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, '') ; – Vince Hill May 18 '23 at 23:00

score 24 · Answer 3 · edited May 22 '18 at 14:48

24

Well, nothing I used worked, so I decided creating a real simple split function, hope it helps:

DECLARE inipos INTEGER;
DECLARE endpos INTEGER;
DECLARE maxlen INTEGER;
DECLARE item VARCHAR(100);
DECLARE delim VARCHAR(1);

SET delim = '|';
SET inipos = 1;
SET fullstr = CONCAT(fullstr, delim);
SET maxlen = LENGTH(fullstr);

REPEAT
    SET endpos = LOCATE(delim, fullstr, inipos);
    SET item =  SUBSTR(fullstr, inipos, endpos - inipos);

    IF item <> '' AND item IS NOT NULL THEN           
        USE_THE_ITEM_STRING;
    END IF;
    SET inipos = endpos + 1;
UNTIL inipos >= maxlen END REPEAT;

edited May 22 '18 at 14:48

Giacomo1968

25,759
11
71
103

answered Aug 13 '13 at 20:01

Jonathan

4,724
7
45
65

You must also check for the endpos for the last item; because for the last item endpos would be zero and this while loop would continue for ever. My recommendation would be set a boolean var initailly to false and settting it to true on endpos = 0 and in the exit until loop and check for this flag = true. – Adeel Raza Azeemi Jul 14 '21 at 12:54

score 20 · Answer 4 · answered Feb 19 '13 at 07:30

20

You can use bewlo one also:

SELECT SUBSTRING_INDEX(Name, ' ', 1) AS fname,
SUBSTRING_INDEX(SUBSTRING_INDEX(Name,' ', 2), ' ',-1) AS mname,
SUBSTRING_INDEX(Name, ' ', -1) as lname FROM mytable;

answered Feb 19 '13 at 07:30

user2001117

3,727
1
18
18

2

This worked for me. I feel like its bad practice, but I ran the same query duplicating my case statement and saw that this approach is twice as fast. Thanks. – wheeleruniverse May 11 '18 at 10:32

score 19 · Answer 5 · answered Oct 21 '14 at 16:15

19

Here is the split function I use:

--
-- split function
--    s   : string to split
--    del : delimiter
--    i   : index requested
--

DROP FUNCTION IF EXISTS SPLIT_STRING;

DELIMITER $

CREATE FUNCTION 
   SPLIT_STRING ( s VARCHAR(1024) , del CHAR(1) , i INT)
   RETURNS VARCHAR(1024)
   DETERMINISTIC -- always returns same results for same input parameters
    BEGIN

        DECLARE n INT ;

        -- get max number of items
        SET n = LENGTH(s) - LENGTH(REPLACE(s, del, '')) + 1;

        IF i > n THEN
            RETURN NULL ;
        ELSE
            RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(s, del, i) , del , -1 ) ;        
        END IF;

    END
$

DELIMITER ;


SET @agg = "G1;G2;G3;G4;" ;

SELECT SPLIT_STRING(@agg,';',1) ;
SELECT SPLIT_STRING(@agg,';',2) ;
SELECT SPLIT_STRING(@agg,';',3) ;
SELECT SPLIT_STRING(@agg,';',4) ;
SELECT SPLIT_STRING(@agg,';',5) ;
SELECT SPLIT_STRING(@agg,';',6) ;

answered Oct 21 '14 at 16:15

Olivier Delrieu

742
6
16

This worked fine for me, and did not cause an infinite loop like accepted answer (not clear why) - but it did not work with whitespace as the separator. For anyone stumbling upon this - look at https://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two#2696901 instead. – Max Dec 08 '17 at 08:24
In case if your delimiter is greater than one, then you also need to divide the length difference by your delimiter length. – Asad Jivani Mar 03 '21 at 09:08
change definition from `SPLIT_STRING ( s VARCHAR(1024) , del CHAR(1) , i INT)` to `SPLIT_STRING ( s VARCHAR(1024) , del VARCHAR(1) , i INT)` and it works with spaces – Marek Lisiecki Jan 27 '22 at 15:09

score 3 · Answer 6 · edited May 29 '19 at 11:47

3

select (case when locate('(', LocationName) = 0 
        then 
            horse_name
        else 
           left(LocationName, locate('(', LocationName) - 1)
       end) as Country            
from   tblcountry;

edited May 29 '19 at 11:47

Tirath

2,294
18
27

answered Sep 20 '18 at 06:24

Farookh Mansuri

41
2

score 3 · Answer 7 · edited Oct 31 '19 at 15:49

concat(upper(substring(substring_index(NAME, ' ', 1) FROM 1 FOR 1)), lower(substring(substring_index(NAME, ' ', 1) FROM 2 FOR length(substring_index(NAME, ' ', 1))))) AS fname,
CASE 
WHEN length(substring_index(substring_index(NAME, ' ', 2), ' ', -1)) > 2 THEN 
  concat(upper(substring(substring_index(substring_index(NAME, ' ', 2), ' ', -1) FROM 1 FOR 1)), lower(substring(substring_index(substring_index(f.nome, ' ', 2), ' ', -1) FROM 2 FOR length(substring_index(substring_index(f.nome, ' ', 2), ' ', -1)))))
  ELSE 
  CASE 
  WHEN length(substring_index(substring_index(f.nome, ' ', 3), ' ', -1)) > 2 THEN 
    concat(upper(substring(substring_index(substring_index(f.nome, ' ', 3), ' ', -1) FROM 1 FOR 1)), lower(substring(substring_index(substring_index(f.nome, ' ', 3), ' ', -1) FROM 2 FOR length(substring_index(substring_index(f.nome, ' ', 3), ' ', -1)))))
  END 
END 
AS mname

score 2 · Answer 8 · edited Dec 09 '15 at 13:27

To get the rest of the string after the second instance of the space delimiter

SELECT
   SUBSTRING_INDEX(SUBSTRING_INDEX('Sachin ramesh tendulkar', ' ', 1), ' ', -1) AS first_name, 
       SUBSTRING_INDEX(SUBSTRING_INDEX('Sachin ramesh tendulkar', ' ', 2), ' ', -1) 
           AS middle_name,
   SUBSTRING('Sachin ramesh tendulkar',LENGTH(SUBSTRING_INDEX('Sachin ramesh tendulkar', ' ', 2))+1) AS last_name

Gildonei · Answer 9 · 2017-06-28T12:27:05.490

SELECT
    p.fullname AS 'Fullname',
    SUBSTRING_INDEX(p.fullname, ' ', 1) AS 'Firstname',
    SUBSTRING(p.fullname, LOCATE(' ',p.fullname), 
        (LENGTH(p.fullname) - (LENGTH(SUBSTRING_INDEX(p.fullname, ' ', 1)) + LENGTH(SUBSTRING_INDEX(p.fullname, ' ', -1))))
    ) AS 'Middlename',
    SUBSTRING_INDEX(p.fullname, ' ', -1) AS 'Lastname',
    (LENGTH(p.fullname) - LENGTH(REPLACE(p.fullname, ' ', '')) + 1) AS 'Name Qt'
FROM people AS p
LIMIT 100;

Explaining:

Find firstname and lastname are easy, you have just to use SUBSTR_INDEX function Magic happens in middlename, where was used SUBSTR with Locate to find the first space position and LENGTH of fullname - (LENGTH firstname + LENGTH lastname) to get all the middlename.

Note that LENGTH of firstname and lastname were calculated using SUBSTR_INDEX

score 1 · Answer 10 · answered Apr 27 '16 at 07:26

1

You could use the common_schema and use the tokenize function. For more information about this, follow the links. Your code the would end up like:

call tokenize(name, ' ');

However, be aware that a space is not a reliable separator for first and last name. E.g. In Spain it is common to have two last names.

answered Apr 27 '16 at 07:26

ToBe_HH

678
6
9

I was not aware of this library, fantastic tip, thx – shikida Dec 31 '21 at 14:27

score 1 · Answer 11 · answered Oct 09 '16 at 04:41

CREATE DEFINER=`root`@`localhost` FUNCTION `getNameInitials`(`fullname` VARCHAR(500), `separator` VARCHAR(1)) RETURNS varchar(70) CHARSET latin1
    DETERMINISTIC
BEGIN
DECLARE `result` VARCHAR(500) DEFAULT '';
DECLARE `position` TINYINT;



SET `fullname` = TRIM(`fullname`);

SET `position` = LOCATE(`separator`, `fullname`);

IF NOT `position`
THEN RETURN LEFT(`fullname`,1);
END IF;

SET `fullname` = CONCAT(`fullname`,`separator`);
SET `result` = LEFT(`fullname`, 1);

cycle: LOOP
    SET `fullname` = SUBSTR(`fullname`, `position` + 1);
    SET `position` = LOCATE(`separator`, `fullname`);

    IF NOT `position` OR NOT LENGTH(`fullname`)
    THEN LEAVE cycle;
    END IF;

    SET `result` = CONCAT(`result`,LEFT(`fullname`, 1));
   -- SET `result` = CONCAT_WS(`separator`, `result`, `buffer`);
END LOOP cycle;

RETURN upper(`result`);
END

1.Execute this function in mysql. 2.this will create a function. Now you can use this function anywhere you want.

 SELECT `getNameInitials`('Kaleem Ul Hassan', ' ') AS `NameInitials`;

3. The above getNameInitails first parameter is string you want to filter and second is the spectator character on which you want to separate you string. 4. In above example 'Kaleem Ul Hassan' is name and i want to get initials and my separator is space ' '.

score 1 · Answer 12 · answered Jul 10 '18 at 07:37

We have stored the value of course Name and chapter name in single column ChapterName.

Value stored like : " JAVA : Polymorphism "

you need to retrieve CourseName : JAVA and ChapterName : Polymorphism

Below is the SQL select query to retrieve .

       SELECT   
          SUBSTRING_INDEX(SUBSTRING_INDEX(ChapterName, ' ', 1), ' ', -1) AS 
       CourseName,

       REPLACE(TRIM(SUBSTR(ChapterName, LOCATE(':', ChapterName)) ),':','') AS 
       ChapterName
       FROM Courses where `id`=1;

Please let me know if any question on this.

score 1 · Answer 13 · answered Sep 16 '19 at 15:38

Combined a few answers here to create a SP that returns the parts of the string.

drop procedure if exists SplitStr;
DELIMITER ;;
CREATE PROCEDURE `SplitStr`(IN Str VARCHAR(2000), IN Delim VARCHAR(1))  
    BEGIN
        DECLARE inipos INT;
        DECLARE endpos INT;
        DECLARE maxlen INT;
        DECLARE fullstr VARCHAR(2000);
        DECLARE item VARCHAR(2000);
        create temporary table if not exists tb_split
        (
            item varchar(2000)
        );



        SET inipos = 1;
        SET fullstr = CONCAT(Str, delim);
        SET maxlen = LENGTH(fullstr);

        REPEAT
            SET endpos = LOCATE(delim, fullstr, inipos);
            SET item =  SUBSTR(fullstr, inipos, endpos - inipos);

            IF item <> '' AND item IS NOT NULL THEN           
                insert into tb_split values(item);
            END IF;
            SET inipos = endpos + 1;
        UNTIL inipos >= maxlen END REPEAT;

        SELECT * from tb_split;
        drop table tb_split;
    END;;
DELIMITER ;

score 0 · Answer 14 · answered Jun 22 '14 at 14:58

To get the rest of the string after the second instance of the space delimiter:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(MsgRest, ' ', 1), ' ', -1) AS EMailID
,  SUBSTRING_INDEX(SUBSTRING_INDEX(MsgRest, ' ', 2), ' ', -1) AS DOB
,  IF(
    LOCATE(' ', `MsgRest`) > 0,
    TRIM(SUBSTRING(SUBSTRING(`MsgRest`, LOCATE(' ', `MsgRest`) +1), 
         LOCATE(' ', SUBSTRING(`MsgRest`, LOCATE(' ', `MsgRest`) +1)) +1)),
    NULL
) AS Person
FROM inbox

score 0 · Answer 15 · edited Sep 28 '16 at 21:59

First Create Procedure as Below:

CREATE DEFINER=`root`@`%` PROCEDURE `sp_split`(str nvarchar(6500), dilimiter varchar(15), tmp_name varchar(50))
BEGIN

    declare end_index   int;
    declare part        nvarchar(6500);
    declare remain_len  int;

    set end_index      = INSTR(str, dilimiter);

    while(end_index   != 0) do

        /* Split a part */
        set part       = SUBSTRING(str, 1, end_index - 1);

        /* insert record to temp table */
        call `sp_split_insert`(tmp_name, part);

        set remain_len = length(str) - end_index;
        set str = substring(str, end_index + 1, remain_len);

        set end_index  = INSTR(str, dilimiter);

    end while;

    if(length(str) > 0) then

        /* insert record to temp table */
        call `sp_split_insert`(tmp_name, str);

    end if;

END

After that create procedure as below:

CREATE DEFINER=`root`@`%` PROCEDURE `sp_split_insert`(tb_name varchar(255), tb_value nvarchar(6500))
BEGIN
    SET @sql = CONCAT('Insert Into ', tb_name,'(item) Values(?)'); 
    PREPARE s1 from @sql;
    SET @paramA = tb_value;
    EXECUTE s1 USING @paramA;
END

How call test

CREATE DEFINER=`root`@`%` PROCEDURE `test_split`(test_text nvarchar(255))
BEGIN

    create temporary table if not exists tb_search
        (
            item nvarchar(6500)
        );

    call sp_split(test_split, ',', 'tb_search');

    select * from tb_search where length(trim(item)) > 0;

    drop table tb_search;

END


call `test_split`('Apple,Banana,Mengo');

score 0 · Answer 16 · answered May 24 '16 at 06:09

DELIMITER $$

DROP FUNCTION IF EXISTS `split_name`$$

CREATE FUNCTION split_name (p_fullname TEXT, p_part INTEGER)
RETURNS TEXT
    READS SQL DATA
BEGIN
    DECLARE v_words INT UNSIGNED;
    DECLARE v_name TEXT;

    SET p_fullname=RTRIM(LTRIM(p_fullname));

    SET v_words=(SELECT SUM(LENGTH(p_fullname) - LENGTH(REPLACE(p_fullname, ' ', ''))+1));

    IF v_words=1 THEN 
        IF p_part=1 THEN
            SET v_name=p_fullname;
        ELSEIF p_part=2 THEN
            SET v_name=NULL;
        ELSEIF p_part=3 THEN
            SET v_name=NULL;
        ELSE
            SET v_name=NULL;
        END IF; 
    ELSEIF v_words=2 THEN 
        IF p_part=1 THEN
            SET v_name=SUBSTRING(p_fullname, 1, LOCATE(' ', p_fullname) - 1);
        ELSEIF p_part=2 THEN
            SET v_name=SUBSTRING(p_fullname, LOCATE(' ', p_fullname) + 1);
        ELSEIF p_part=3 THEN
            SET v_name=NULL;
        ELSE
            SET v_name=NULL;
        END IF; 
    ELSEIF v_words=3 THEN 
        IF p_part=1 THEN
            SET v_name=SUBSTRING(p_fullname, 1, LOCATE(' ', p_fullname) - 1);
        ELSEIF p_part=2 THEN
            SET p_fullname=SUBSTRING(p_fullname, LOCATE(' ', p_fullname) + 1);
            SET v_name=SUBSTRING(p_fullname, 1, LOCATE(' ', p_fullname) - 1);
        ELSEIF p_part=3 THEN
            SET p_fullname=REVERSE (SUBSTRING(p_fullname, LOCATE(' ', p_fullname) + 1));
            SET p_fullname=SUBSTRING(p_fullname, 1, LOCATE(' ', p_fullname) - 1);
            SET v_name=REVERSE(p_fullname);
        ELSE
            SET v_name=NULL;
        END IF; 
    ELSEIF v_words>3 THEN 
        IF p_part=1 THEN
            SET v_name=SUBSTRING(p_fullname, 1, LOCATE(' ', p_fullname) - 1);
        ELSEIF p_part=2 THEN
            SET p_fullname=REVERSE(SUBSTRING(p_fullname, LOCATE(' ', p_fullname) + 1));
            SET p_fullname=SUBSTRING(p_fullname, LOCATE(' ', p_fullname,SUBSTRING_INDEX(p_fullname,' ',1)+1) + 1);
            SET v_name=REVERSE(p_fullname);
        ELSEIF p_part=3 THEN
            SET p_fullname=REVERSE (SUBSTRING(p_fullname, LOCATE(' ', p_fullname) + 1));
            SET p_fullname=SUBSTRING(p_fullname, 1, LOCATE(' ', p_fullname) - 1);
            SET v_name=REVERSE(p_fullname);
        ELSE
            SET v_name=NULL;
        END IF;
    ELSE
        SET v_name=NULL;
    END IF;
 RETURN v_name; 
END;

SELECT split_name('Md. Obaidul Haque Sarker',1) AS first_name,
split_name('Md. Obaidul Haque Sarker',2) AS middle_name,
split_name('Md. Obaidul Haque Sarker',3) AS last_name

score 0 · Answer 17 · answered Jan 27 '22 at 15:50

Based on previous answers and do some modifications...

note:

p_delimiter has to be VARCHAR(1) couse CHAR(1) if is ' ' (space) gives '' (removes space and returns empty string)

Instead use of LENGTH I prefer CHAR_LENGTH which is safe for multibyte characters like UTF

DROP FUNCTION IF EXISTS FN_SPLIT_STR;

DELIMITER ;;

CREATE FUNCTION FN_SPLIT_STR(
    p_input VARCHAR(2000), p_delimiter VARCHAR(1), p_position INT
)
    RETURNS VARCHAR(2000)
    DETERMINISTIC
BEGIN
    DECLARE chunks INT;
    SET chunks := CHAR_LENGTH(p_input) - CHAR_LENGTH(REPLACE(p_input, p_delimiter, '')) + 1;

    IF p_position > chunks THEN
        RETURN NULL;
    END IF;

    RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(p_input, p_delimiter, p_position), p_delimiter, -1);
END;;

DELIMITER ;

score 0 · Answer 18 · answered Jan 09 '23 at 14:43

0

SELECT "SUBSTRING_INDEX(name, ' ', 1) as first_name", "TRIM(REPLACE(name, SUBSTRING_INDEX(name, ' ', 1), '')) as last_name" FROM tbl;

answered Jan 09 '23 at 14:43

Ricky Riccs

41
1
5

2

Please read [answer] and [edit] your answer to contain an explanation as to why this code would actually solve the problem at hand. Always remember that you're not only solving the problem, but are also educating the OP and any future readers of this post – chrslg Jan 11 '23 at 00:49

score 0 · Answer 19 · edited May 31 '23 at 19:17

0

WITH RECURSIVE cte AS
( SELECT 1 AS n, SUBSTRING_INDEX('apple,banana,orange', ',', 1) AS VALUE UNION
 SELECT n + 1, SUBSTRING_INDEX(SUBSTRING_INDEX('apple,banana,orange', ',', n + 1), ',', -1)
  AS VALUE FROM cte 
  WHERE SUBSTRING_INDEX(SUBSTRING_INDEX('apple,banana,orange', ',', n),',',-1) != SUBSTRING_INDEX('apple,banana,orange', ',', -1) )
   SELECT VALUE FROM cte;

edited May 31 '23 at 19:17

rrk

15,677
4
29
45

answered Mar 06 '23 at 04:39

blessy kiran00

1
1

How to split the name string in mysql?

19 Answers19

Function:

Usage:

Example:

Linked

Related