如何在 mysql 中分割名称字符串？

如何在 mysql 中分割名称字符串？

例如:

name
-----
Sachin ramesh tendulkar
Rahul dravid

像 firstname,middlename,lastname那样把名字分开:

firstname   middlename    lastname
---------  ------------   ------------
sachin     ramesh         tendulkar
rahul      dravid
417510 次浏览
小开

在 MySQL 中没有字符串分割函数。所以你必须创建自己的函数。这个能帮你。详情请浏览此网页.

功能:

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, '');

用法:

SELECT SPLIT_STR(string, delimiter, position)

例如:

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


+-------+
| third |
+-------+
| ccc   |
+-------+
小开

我把这个答案分成了两个方法。第一个方法将您的全名字段分为名字、中间名字和姓氏。如果没有中间名，中间名将显示为 NULL。

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

第二个方法将中间名视为姓氏的一部分。我们将只从您的全名字段中选择名字和姓氏列。

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

你可以用 subr、 location、 substring _ index 等来做很多很酷的事情。看看手册上有没有什么疑问。http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

小开

你也可以使用 bewlo one:

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

我用的都不管用所以我决定创建一个简单的分割函数希望能有所帮助:

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;
小开

在空格分隔符的第二个实例之后获取字符串的其余部分:

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
小开

下面是我使用的分割函数:

--
-- 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) ;
小开

首先创建程序如下:

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

在此之后，创建如下程序:

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

如何调用 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');
小开

在空格分隔符的第二个实例之后获取字符串的其余部分

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
小开

您可以使用 Common _ schema和 tokenize函数。有关这方面的更多信息，请点击链接。你的代码结果会是这样的:

call tokenize(name, ' ');

但是，请注意空格不是名和姓的可靠分隔符。例如。在西班牙，有两个姓是很常见的。

小开 

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
小开 
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. 在 mysql 中执行这个函数。 这将创建一个函数。现在你可以在任何地方使用这个函数。

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

3.上面的 getNameInitails 第一个参数是要过滤的字符串，第二个参数是要分隔字符串的旁观者字符。 4.在上面的例子中，‘ Kaleem Ul Hassan’是名字，我想得到首字母，我的分隔符是空格’。

小开 
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;

解释:

查找名和姓很容易，只需使用 SUBSTR _ INDEX 函数即可 奇迹发生在中间名中，其中使用 SUBSTR 和 Locate 来查找第一个空间位置和全名的 LENGTH-(LENGTH firstname + LENGTH lastname)来获得所有的中间名。

注意，名和姓的 LENGTH 是使用 SUBSTR _ INDEX 计算的

小开

我们已经将课程名称和章节名称的值存储在单列“章节名称”中。

像“ JAVA: 多态性”这样存储的值

您需要检索 CourseName: JAVA 和 ChapterName: Polyformism

下面是要检索的 SQL 选择查询。

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


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

如果有任何问题，请告诉我。

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

在这里组合一些答案来创建一个返回字符串部分的 SP。

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 ;
小开 
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
小开

根据之前的答案，做一些修改。

注:

如果是 ' '(空格)给出 ''(移除空格并返回空字符串) ，则 p_delimiter必须是 VARCHAR(1)

相对于使用 LENGTH，我更喜欢使用 CHAR_LENGTH，它对于像 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 ;

提交答案