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 17 Answers
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.
8There 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 | +-------+ 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; 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) ; 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; select (case when locate('(', LocationName) = 0 then horse_name else left(LocationName, locate('(', LocationName) - 1) end) as Country from tblcountry; 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 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 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
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.
1CREATE 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 ' '.
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.
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 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 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'); 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 ; 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 ; 
