How to split the name string in mysql

Question

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  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

User · Answer

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            gt 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

User · Answer

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    gt  0      TRIM SUBSTRING SUBSTRING  MsgRest   LOCATE       MsgRest    1             LOCATE      SUBSTRING  MsgRest   LOCATE       MsgRest    1    1        NULL   AS Person FROM inbox

User · Answer

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  lt  gt     AND item IS NOT NULL THEN                    USE THE ITEM STRING      END IF      SET inipos   endpos   1  UNTIL inipos  gt   maxlen END REPEAT

User · Answer

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      -------

User · Answer

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

User · Answer

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   gt  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    gt  0       drop table tb search   END   call  test split   Apple Banana Mengo

User · Answer

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    gt  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    gt  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

User · Answer

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

User · Answer

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  lt  gt     AND item IS NOT NULL THEN                            insert into tb split values item               END IF              SET inipos   endpos   1          UNTIL inipos  gt   maxlen END REPEAT           SELECT   from tb split          drop table tb split      END   DELIMITER

User · Answer

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

User · Answer

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

User · Answer

select  case when locate      LocationName    0          then              horse name         else             left LocationName  locate      LocationName  - 1         end  as Country             from   tblcountry

User · Answer

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 gt 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

[mysql] How to split the name string in mysql?

Examples related to mysql

Examples related to sql

Examples related to split

Examples related to substring