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 |
SELECT
`productCode`,
SUBSTRING_INDEX( `productCode`, ' ', 1 ),
SUBSTRING_INDEX( `productCode`, ' ', -1 )
FROM tblSubmissionProducts;
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)]>