How to split the name string in mysql?
MysqlSqlSplitSubstringMysql Problem Overview
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 Solutions
Solution 1 - Mysql
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
Solution 2 - Mysql
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 |
+-------+
Solution 3 - Mysql
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;
Solution 4 - Mysql
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) ;
Solution 5 - Mysql
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;
Solution 6 - Mysql
select (case when locate('(', LocationName) = 0
then
horse_name
else
left(LocationName, locate('(', LocationName) - 1)
end) as Country
from tblcountry;
Solution 7 - Mysql
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
Solution 8 - Mysql
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
Solution 9 - Mysql
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
Solution 10 - Mysql
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.
Solution 11 - Mysql
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 ' '.
Solution 12 - Mysql
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.
Solution 13 - Mysql
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
Solution 14 - Mysql
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');
Solution 15 - Mysql
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
Solution 16 - Mysql
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 ;
Solution 17 - Mysql
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 ;