카테고리 없음

[mysql] mysql에서 이름 문자열을 분할하는 방법은 무엇입니까?

필살기쓰세요 2021. 2. 18. 13:15

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