Как разбить строку имени в mysql?
Например:
name
-----
Sachin ramesh tendulkar
Rahul dravid
Разделите имя как firstname,middlename,lastname:
firstname middlename lastname
--------- ------------ ------------
sachin ramesh tendulkar
rahul dravid
Я разделил этот ответ на два (2) метода. Первый метод будет разделять ваше поле полного имени на первое, среднее и фамильное. Среднее имя будет отображаться как 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
Этот второй метод рассматривает среднее имя как часть последнего имени. Мы будем выбирать только поле firstname и lastname из вашего поля fullname.
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(fullname, ' ', 1), ' ', -1) AS first_name,
TRIM( SUBSTR(fullname, LOCATE(' ', fullname)) ) AS last_name
FROM registeredusers
Там есть куча интересных вещей, которые вы можете сделать с помощью substr, locate, substring_index и т.д. Проверьте руководство для некоторой реальной путаницы. http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
Ну, я ничего не использовал, поэтому решил создать настоящую простую функцию разделения, надеюсь, что это поможет:
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;
Вот функция разделения, которую я использую:
--
-- 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) ;
Вы также можете использовать bewlo:
SELECT SUBSTRING_INDEX(Name, ' ', 1) AS fname,
SUBSTRING_INDEX(SUBSTRING_INDEX(Name,' ', 2), ' ',-1) AS mname,
SUBSTRING_INDEX(Name, ' ', -1) as lname FROM mytable;
Чтобы получить остальную часть строки после второго экземпляра разделителя пробелов
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
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 Магия происходит в middlameame, где был использован SUBSTR с Locate, чтобы найти первую позицию в пространстве и LENGTH из полного имени - (LENGTH firstname + LENGTH lastname), чтобы получить все имя middlame.
Обратите внимание, что LENGTH первого имени и последнего имени были рассчитаны с использованием SUBSTR_INDEX
В MySQL нет функции разделения строк. поэтому вам нужно создать свою собственную функцию. Используйте ниже ссылку. Это поможет вам
http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/
Чтобы получить остальную часть строки после второго экземпляра разделителя пространства:
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
Сначала создайте процедуру ниже:
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
Как проверить вызов
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');
Вы можете использовать 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. 2.Это создаст функцию. Теперь вы можете использовать эту функцию в любом месте.
SELECT `getNameInitials`('Kaleem Ul Hassan', ' ') AS `NameInitials`;
3. Вышеуказанный getNameInitails первый параметр - это строка, которую вы хотите фильтровать, а второй - символ зрителя, по которому вы хотите разделить строку. 4. В приведенном выше примере "Kaleem Ul Hassan" - это имя, и я хочу получить инициалы, а мой разделитель - это пробел ".