MySQL: How do I split a value into 3 possible parts divided by space

Question

I have a column which can have 1, 2 or 3 parts divided by space.
I need to extract each part "if" it exists (string will always contain at least the first part).

Example:

1: Apple

2: Apple Orange

3: Apple Pear lemon





|1| Apple             | Apple |        |        |

|2| Apple Orange      | Apple | Orange |        |

|3| Pear lemon Orange | Pear  | lemon  | Orange |

I can extract the 1st part and the 3rd part
(but this inserts the same value in 3rd column if there is only one part).

SELECT

`productCode`,

SUBSTRING_INDEX( `productCode`, ' ', 1 ),

SUBSTRING_INDEX( `productCode`, ' ', -1 )

FROM tblSubmissionProducts;

Answer 1

you can use this to split a string:

SELECT 
  REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX('This is a Test', ' ', 1)),' ',1)) AS n1,
  REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX('This is a Test', ' ', 2)),' ',1)) AS n2,
  REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX('This is a Test', ' ', 3)),' ',1)) AS n3,
  REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX('This is a Test', ' ', 4)),' ',1)) AS n4;

Sample

MariaDB [(none)]> SELECT
    ->   REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX('This is a Test', ' ', 1)),' ',1)) AS n1,
    ->   REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX('This is a Test', ' ', 2)),' ',1)) AS n2,
    ->   REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX('This is a Test', ' ', 3)),' ',1)) AS n3,
    ->   REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX('This is a Test', ' ', 4)),' ',1)) AS n4;
+------+----+----+------+
| n1   | n2 | n3 | n4   |
+------+----+----+------+
| This | is | a  | Test |
+------+----+----+------+
1 row in set (0.00 sec)

MariaDB [(none)]>

changed Answer

SELECT 
  TRIM(REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT('Apple', '    '), ' ', 1)),' ',1))) AS n1,
  TRIM(REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT('Apple', '    '), ' ', 2)),' ',1))) AS n2,
  TRIM(REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT('Apple', '    '), ' ', 3)),' ',1))) AS n1;

better Sample

MariaDB [(none)]> SELECT
    ->   TRIM(REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT('Apple', '    '), ' ', 1)),' ',1))) AS n1,
    ->   TRIM(REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT('Apple', '    '), ' ', 2)),' ',1))) AS n2,
    ->   TRIM(REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT('Apple', '    '), ' ', 3)),' ',1))) AS n1;
+-------+----+----+
| n1    | n2 | n1 |
+-------+----+----+
| Apple |    |    |
+-------+----+----+
1 row in set (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]> SELECT
    ->   TRIM(REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT('Apple Orange', '    '), ' ', 1)),' ',1))) AS n1,
    ->   TRIM(REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT('Apple Orange', '    '), ' ', 2)),' ',1))) AS n2,
    ->   TRIM(REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT('Apple Orange', '    '), ' ', 3)),' ',1))) AS n1;
+-------+--------+----+
| n1    | n2     | n1 |
+-------+--------+----+
| Apple | Orange |    |
+-------+--------+----+
1 row in set (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]> SELECT
    ->   TRIM(REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT('Pear lemon Orange', '    '), ' ', 1)),' ',1))) AS n1,
    ->   TRIM(REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT('Pear lemon Orange', '    '), ' ', 2)),' ',1))) AS n2,
    ->   TRIM(REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT('Pear lemon Orange', '    '), ' ', 3)),' ',1))) AS n1;
+------+-------+--------+
| n1   | n2    | n1     |
+------+-------+--------+
| Pear | lemon | Orange |
+------+-------+--------+
1 row in set (0.00 sec)

MariaDB [(none)]>

MySQL: How do I split a value into 3 possible parts divided by space

Sign up