nads nads - 7 months ago 21
SQL Question

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

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

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)]>
Comments