CODEWITHSUNDEEP
mysqlsql
Nyfiken
Nyfiken

Reputation: 33

MySQL separate data to two new columns

One column contains a comma between data and I want to show these different data in two new columns to my question. Examples of data, column CarAndYear: [Volvo, 1995] I want the 'Volvo' to be displayed in the column Cars and the year '1995 'in the column Year. I would be very grateful for help with this MySQL issue.

Upvotes: 0

Views: 442

Answers (2)

peterm
peterm

Reputation: 92785

Assuming that you always have leading and trailing brackets:

SELECT TRIM(SUBSTR(CarAndYear, 2, INSTR(CarAndYear, ',') - 2)) Cars, 
       TRIM(SUBSTR(CarAndYear, INSTR(CarAndYear, ',') + 1, LENGTH(CarAndYear) - INSTR(CarAndYear, ',') -1)) `Year`
FROM (SELECT '[Volvo, 1995]' CarAndYear) n

Output

+-------+-------+
| Cars  | Year  |
+-------+-------+
| Volvo |  1995 |
+-------+-------+

Upvotes: 1

StarPinkER
StarPinkER

Reputation: 14271

SELECT 
    SUBSTRING_INDEX(TRIM(LEADING '[' FROM `columnname`),',',1) as CARS,
    SUBSTRING_INDEX(TRIM(TRAILING ']' FROM `columnname`),',',-1) as Year 
FROM `tablename`;

SUBSTRING_INDEX in mysql

TRIM in mysql

Upvotes: 1

Related Questions