mysql a column into multiple columns based on the value

Question

mysql> select description from devices where id=172;
+--------------------------------------------------------------------------------+
| description                                                                    |
+--------------------------------------------------------------------------------+
| Fault: 'HYD OIL TEMP HIGH' from 'Controller' of type 'SYSTEM' with code '1003' |
+--------------------------------------------------------------------------------+
1 row in set (0.01 sec)

The above is the pattern in which I have records in my database. I am trying to split them into multiple columns like this just for running a report.

+---------+------------------------+--------------+---------+---------+
| status  |  description           | device       | system  |   code  |
+---------+------------------------+--------------+---------+---------+
| Fault   | HYD HYD OIL TEMP HIGH  | controller   | SYSTEM  | 1003    |
+---------+------------------------+--------------+---------+---------+

I know the above stuff which I am doing is worse. I don't want to change the ETL for running a one time report. This is what I have tried.

mysql> select substr(description, 1, locate(":", description)-1) as status from devices where id=172;
+--------+
| status |
+--------+
| Fault  |
+--------+
1 row in set (0.00 sec)

The database is over amazon RDS. So I cannot use lib_mysqludf_preg. I can only do plain SQL. Help is appreciated. Thanks.

Why do you need to perform these manipulations? Will you then be using the resulting columns in SQL, or is it purely for presentation? If the latter, you could (and probably should) undertake such within your application. — eggyal
– eggyal, Commented Jun 7, 2012 at 4:49
I am just trying to analyse a crash report, its hard to catch it with the existing format. But yeah, if i had an app I would be doing it there and not in the sql layer. — Deepak
– Deepak, Commented Jun 7, 2012 at 8:14

Devart · Accepted Answer · 2012-06-07 06:23:59Z

1

Try this one -

SELECT
  SUBSTRING_INDEX(description, ':', 1) status,
  SUBSTRING_INDEX(SUBSTRING_INDEX(description, '''', 2), '''', -1) description,
  SUBSTRING_INDEX(SUBSTRING_INDEX(description, '''', 4), '''', -1) device,
  SUBSTRING_INDEX(SUBSTRING_INDEX(description, '''', 6), '''', -1) system,
  SUBSTRING_INDEX(SUBSTRING_INDEX(description, '''', 8), '''', -1) code
FROM
  devices
WHERE
  id = 172;

answered Jun 7, 2012 at 6:23

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt Dodge · Accepted Answer · 2012-06-07 05:36:30Z

0

It's not going to be easy, most likely doing it on the app side would be best. But if you need to do it with native SQL, you should probably try and use a combination of MID and LOCATE. Those will give you substrings and locations of strings so it would assume that the format of the string is going to be consistent.

You have the status field done, here I will give you description and device, you should get the idea of how to finish it off (st is the description string)

SELECT 
    MID(st, LOCATE(': ',st) + 3, LOCATE(' from ',st) - LOCATE(': ',st) - 4) AS description,
    MID(st, LOCATE(' from ',st) + 7, LOCATE(' of type ',st) - LOCATE(' from ',st) - 8) AS device
FROM yourtable

answered Jun 7, 2012 at 5:36

Matt Dodge

11.2k7 gold badges42 silver badges58 bronze badges

Collectives™ on Stack Overflow

mysql a column into multiple columns based on the value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related