MySQL 如何在mysql中拆分名称字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14950466/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to split the name string in mysql?
提问by Madhav
How to split the name string in mysql ?
如何在mysql中拆分名称字符串？
E.g.:
例如：
name
-----
Sachin ramesh tendulkar
Rahul dravid
Split the name like firstname,middlename,lastname:
将名称拆分为firstname,middlename,lastname：
firstname middlename lastname
--------- ------------ ------------
sachin ramesh tendulkar
rahul dravid
回答by Jesse C
I've seperated 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.
我已将此答案分为两（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
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
你可以用 substr、locate、substring_index 等做很多很酷的事情。检查手册是否有一些真正的困惑。http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
回答by Jonathan
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;
回答by Olivier Delrieu
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) ;
回答by Rahul Chipad
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.
MySQL 中没有字符串拆分功能。所以你必须创建自己的函数。这会帮助你。更多详情请访问此链接。
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 |
+-------+
回答by user2001117
You can use bewlo one also:
您也可以使用 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;
回答by Farookh Mansuri
select (case when locate('(', LocationName) = 0
then
horse_name
else
left(LocationName, locate('(', LocationName) - 1)
end) as Country
from tblcountry;
回答by Patnaidu Landa
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
回答by user3867306
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.
1.在mysql中执行该函数。2.这将创建一个函数。现在你可以在任何你想要的地方使用这个功能。
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 ' '.
3. 上面的 getNameInitails 第一个参数是您要过滤的字符串，第二个是您要在其上分隔字符串的旁观者字符。4. 在上面的例子中，'Kaleem Ul Hassan' 是名字，我想得到姓名首字母，我的分隔符是空格 ' '。
回答by Junior
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.
查找名字和姓氏很容易，您只需使用 SUBSTR_INDEX 函数魔术发生在中间名中，其中使用 SUBSTR 和 Locate 来查找全名的第一个空格位置和长度 - (LENGTH firstname + LENGTH lastname) 以获取所有中间名。
Note that LENGTH of firstname and lastname were calculated using SUBSTR_INDEX
请注意，名字和姓氏的 LENGTH 是使用 SUBSTR_INDEX 计算的
回答by Bikash Ranjan
We have stored the value of course Name and chapter name in single column ChapterName.
我们已经将课程名称和章节名称的值存储在单列 ChapterName 中。
Value stored like : " JAVA : Polymorphism "
值存储为：“JAVA：多态性”
you need to retrieve CourseName : JAVA and ChapterName : Polymorphism
你需要检索 CourseName : JAVA 和 ChapterName : Polymorphism
Below is the SQL select query to retrieve .
下面是要检索的 SQL 选择查询。
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.
如果对此有任何疑问，请告诉我。
