How to Split the Name String in MySQL

Splitting a single column (name) into two (forename, surname) in SQL

A quick solution is to use SUBSTRING_INDEX to get everything at the left of the first space, and everything past the first space:

UPDATE tablename
SET
  Forename = SUBSTRING_INDEX(Name, ' ', 1),
  Surname = SUBSTRING_INDEX(Name, ' ', -1)

Please see fiddle here. It is not perfect, as a name could have multiple spaces, but it can be a good query to start with and it works for most names.

Creating a MYSQL query from results of splitting a string and using LIKE to match keywords in PHP

The problem is here $new_search = preg_split("/,/", $my_search); use $new_search = preg_split("/, /", $my_search); instead.

The items in the string are separated by a comma and a space (", ") so you should split the string with that.

String Split function in MySQL

It is possible:

-- Preparation
CREATE TABLE tb_Digits (d int(11) UNSIGNED NOT NULL PRIMARY KEY);
INSERT tb_Digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
INSERT IGNORE tb_Digits
SELECT o1.d+o2.d*10+o3.d*100+o4.d*1000 d FROM tb_Digits o4, tb_Digits o3, tb_Digits o2, tb_Digits o1;

CREATE PROCEDURE pc_splitStr(IN in_string TEXT, IN in_separator CHAR(1))
COMMENT 'Use (SELECT word FROM return_splitStr) para ver o resultado'
BEGIN
  DECLARE o_limit INT(11) UNSIGNED DEFAULT LENGTH(in_string)-LENGTH(REPLACE(in_string,in_separator,''))+1;
  DROP TABLE IF EXISTS return_splitStr;
  CREATE TEMPORARY TABLE return_splitStr (word TEXT);
  INSERT return_splitStr
  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(in_string, in_separator, d.d), in_separator, -1)
  FROM tb_Digits d
  WHERE d.d>0 AND d.d<=o_limit;
END;

-- Execution
CALL pc_splitStr('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',',');
SELECT word FROM return_splitStr;

MySQL Split string by character mutli-part

You use SUBSTRING() function in mysql.

For example.

mysql> SELECT SUBSTRING('habonytest', -3);
      -> est
 mysql> SELECT SUBSTRING('habonytest', -5, 2);
      -> yt
 mysql> SELECT SUBSTRING('habonytest', FROM -5 FOR 4);
      -> ytes

Also mysql has SUBSTRING_INDEX() function.

example:

mysql> SELECT SUBSTRING_INDEX('myid@domain.com', '@', -1);
      -> domain.com
 mysql> SELECT SUBSTRING_INDEX('myid@domain.com', '@', 1);
      -> myid

See it more detail

String split of IDs and concat with names in MySQL

Storing delimited fields in a table is almost always a bad idea.

However if the field is delimited with commas then you can use the FIND_IN_SET function. If desperate you can use REPLACE to change the delimiters to commas within the FIND_IN_SET function:-

SELECT a.c1,
        a.ids,
        GROUP_CONCAT(b.name SEPARATOR '|')
FROM Table1 a
INNER JOIN Table2 b
ON FIND_IN_SET(b.id, REPLACE(a.ids, '|', ','))
GROUP BY a.c1,
        a.ids

MySQL : left part of a string split by a separator string?

SELECT SUBSTRING_INDEX(column_name, '==', 1) FROM table ; // for left

SELECT SUBSTRING_INDEX(column_name, '==', -1) FROM table; // for right

Can you split/explode a field in a MySQL query?

MySQL's only string-splitting function is SUBSTRING_INDEX(str, delim, count). You can use this, to, for example:

Return the item before the first separator in a string:

mysql> SELECT SUBSTRING_INDEX('foo#bar#baz#qux', '#', 1);
+--------------------------------------------+
| SUBSTRING_INDEX('foo#bar#baz#qux', '#', 1) |
+--------------------------------------------+
| foo                                        |
+--------------------------------------------+
1 row in set (0.00 sec)

Return the item after the last separator in a string:

mysql> SELECT SUBSTRING_INDEX('foo#bar#baz#qux', '#', -1);
+---------------------------------------------+
| SUBSTRING_INDEX('foo#bar#baz#qux', '#', -1) |
+---------------------------------------------+
| qux                                         |
+---------------------------------------------+
1 row in set (0.00 sec)

Return everything before the third separator in a string:

mysql> SELECT SUBSTRING_INDEX('foo#bar#baz#qux', '#', 3);
+--------------------------------------------+
| SUBSTRING_INDEX('foo#bar#baz#qux', '#', 3) |
+--------------------------------------------+
| foo#bar#baz                                |
+--------------------------------------------+
1 row in set (0.00 sec)

Return the second item in a string, by chaining two calls:

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('foo#bar#baz#qux', '#', 2), '#', -1);
+----------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('foo#bar#baz#qux', '#', 2), '#', -1) |
+----------------------------------------------------------------------+
| bar                                                                  |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

In general, a simple way to get the nth element of a #-separated string (assuming that you know it definitely has at least n elements) is to do:

SUBSTRING_INDEX(SUBSTRING_INDEX(your_string, '#', n), '#', -1);

The inner SUBSTRING_INDEX call discards the nth separator and everything after it, and then the outer SUBSTRING_INDEX call discards everything except the final element that remains.

If you want a more robust solution that returns NULL if you ask for an element that doesn't exist (for instance, asking for the 5th element of 'a#b#c#d'), then you can count the delimiters using REPLACE and then conditionally return NULL using IF():

IF(
    LENGTH(your_string) - LENGTH(REPLACE(your_string, '#', '')) / LENGTH('#') < n - 1,
    NULL,
    SUBSTRING_INDEX(SUBSTRING_INDEX(your_string, '#', n), '#', -1)
)

Of course, this is pretty ugly and hard to understand! So you might want to wrap it in a function:

CREATE FUNCTION split(string TEXT, delimiter TEXT, n INT)
RETURNS TEXT DETERMINISTIC
RETURN IF(
    (LENGTH(string) - LENGTH(REPLACE(string, delimiter, ''))) / LENGTH(delimiter) < n - 1,
    NULL,
    SUBSTRING_INDEX(SUBSTRING_INDEX(string, delimiter, n), delimiter, -1)
);

You can then use the function like this:

mysql> SELECT SPLIT('foo,bar,baz,qux', ',', 3);
+----------------------------------+
| SPLIT('foo,bar,baz,qux', ',', 3) |
+----------------------------------+
| baz                              |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SPLIT('foo,bar,baz,qux', ',', 5);
+----------------------------------+
| SPLIT('foo,bar,baz,qux', ',', 5) |
+----------------------------------+
| NULL                             |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SPLIT('foo###bar###baz###qux', '###', 2);
+------------------------------------------+
| SPLIT('foo###bar###baz###qux', '###', 2) |
+------------------------------------------+
| bar                                      |
+------------------------------------------+
1 row in set (0.00 sec)