[mysql] 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
-------------------
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 |
+-------+
-------------------
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;
-------------------
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:
이름과 성을 찾기는 쉽습니다. SUBSTR_INDEX 함수를 사용하기 만하면됩니다. Magic은 중간 이름에서 발생하며, 여기서 SUBSTR을 사용하여 첫 번째 공백 위치를 찾고 전체 이름의 LENGTH-(LENGTH firstname + LENGTH lastname) 모든 중간 이름을 가져옵니다.성과 이름의 LENGTH는 SUBSTR_INDEX를 사용하여 계산되었습니다.-------------------
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
-------------------
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'은 이름이고 이니셜을 얻고 싶습니다. 구분자는 공백 ''입니다. -------------------
강좌 이름과 장 이름의 값을 단일 열 ChapterName에 저장했습니다.다음과 같이 저장된 값 : "JAVA : Polymorphism"CourseName : JAVA 및 ChapterName : Polymorphism을 검색해야합니다.아래는 검색 할 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 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
-------------------
당신은 사용할 수
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`@`%` 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');
-------------------
여기에 몇 가지 답변을 결합하여 문자열의 일부를 반환하는 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 ;
출처
https://stackoverflow.com/questions/39940088