cleft cleft - 2 months ago 7
SQL Question

How to split everything after - (dash) using MySQL

I need to split data within a cell separated by

-
(dash) and put into separate columns. The problem I am having is there may be more than one
-
.

So using the table below with the original data coming from
sic_orig
, I need to put everything before the first
-
in
sic_num
and everything after the first
-
in
sic_desc
. I'm sure this is really easy, but I can't seem to find anything clear on this.




This is what my table should look like with
sic_orig
being the source and
sic_num
and
sic_desc
being data pulled from
sic_orig
:

sic_orig | sic_num | sic_desc
---------------------------------------------------------------------------
509406 - Jewelers-Wholesale | 509406 | Jewelers-Wholesale
--------------------------------------|-----------|------------------------
506324 - Burglar Alarm Systems | 506324 | Burglar Alarm Systems
--------------------------------------|-----------|------------------------
502317 - Picture Frames-Wholesale | 502317 | Picture Frames-Wholesale





This code works, but only works right if there are two
-
's and some cells may have 1, 2 or 3
-
's

UPDATE test_tbl_1
SET sic_num = SUBSTRING_INDEX(`sic_orig`, '-', 1),
sic_desc = SUBSTRING_INDEX(`sic_orig`, '-', -2);





How do I split everything before first
-
and everything after first
-
?

Answer

You can use a combination of SUBSTR() and LOCATE() function to help you slice the string:

UPDATE test_tbl_1 
SET sic_num  = SUBSTR(sig_orig, 1, LOCATE('-', sig_orig) - 1),
    sic_desc = SUBSTR(sig_orig, LOCATE('-', sig_orig) +  1) ;

Click here for MySQL string functions.

Comments