How to split the name string in mysql?

2018-12-31 19:25发布

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

标签: mysql sql
14条回答
余生无你
2楼-- · 2018-12-31 19:30

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;
查看更多
余生无你
3楼-- · 2018-12-31 19:30
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

查看更多
与风俱净
4楼-- · 2018-12-31 19:30

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');
查看更多
后来的你喜欢了谁
5楼-- · 2018-12-31 19:34

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   |
+-------+
查看更多
不再属于我。
6楼-- · 2018-12-31 19:35

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.

查看更多
唯独是你
7楼-- · 2018-12-31 19:36

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

查看更多
登录 后发表回答