Table 1

| string 1                               | 
|Zürich, Grüngasse                       | 
|Flagshipstore Thun, Bälliz, Raum 1      | 

The above string needs to be cut into two columns with the string value that appears before the common (,)

Result

| col 1    | col2           |
| Zürich   | Grüngasse      |
|Thun      | Bälliz         |

CodePudding user response：

If you want to separate a column with comma you can use below approach, based on your expected result:

SELECT SUBSTRING_INDEX(column1, ',', 1) as first_column, 
SUBSTRING_INDEX(SUBSTRING_INDEX(column1, ',', 2), ',', -1) as second_column
from table_a;

Demo:

Updated: If you want to check strings that might have less than three values as @Akina mentioned in the comments use:

Credits to user @fthiella: MySQL - Get first 3 comma separated values

 SELECT column1 as tot_column, SUBSTRING_INDEX(column1, ',', 1) AS first_col,
  CASE WHEN LENGTH(column1)-LENGTH(Replace(column1, ',', ''))>0
       THEN SUBSTRING_INDEX(SUBSTRING_INDEX(column1, ',', 2), ',', -1)
       ELSE NULL END AS second_col,
  CASE WHEN LENGTH(column1)-LENGTH(Replace(column1, ',', ''))>1
       THEN SUBSTRING_INDEX(SUBSTRING_INDEX(column1, ',', 3), ',', -1)
       ELSE NULL END AS third_col
FROM
  table_a

CodePudding user response：

If I understand you correctly you want the string before the first comma and the rest of the column seperated into 2 parts

SELECT SUBSTRING_INDEX(column1, ',', 1) as first_column, 
SUBSTRING(column1 FROM LOCATE(',', column1)) as the_rest 
from test;

DEMO DBFiddle

RESULT

| first_column       | the_rest           |
| ------------------ | ------------------ |
| Zürich             | , Grüngasse        |
| London             | ,, Paris, New York |
| Flagshipstore Thun | , Bälliz, Raum 1   |