1

I have a mysql table (in AWS RDS) with field F1, F2 and F3. F1 has strings like 'abc-def'. What I need to do is split the F1 values by hyphen into two strings ('abc' and 'def') and store them into F2 and F3 in the same row.

the record before changing:

F1: abc-def 
F2:
F3:

the record after changing:

F1: abc-def
F2: abc
F3: def

It seems that mysql does support regexp but not back reference. My last resort would be using mysqldump but I want to see if there is any way to do this in mysql directly.

Any reply is appreciated!

fancyPants
  • 50,732
  • 33
  • 89
  • 96
pdgetrf
  • 23
  • 5
  • possible duplicate of [Can you split/explode a field in a MySQL query?](http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query) – Adam Plocher May 14 '13 at 22:22

2 Answers2

0

There is another post that discusses this using commas as the separator found at Can Mysql Split a column?. Just do an update procedure setting the two columns to the substring_index results.

Community
  • 1
  • 1
user2052115
  • 11
  • 1
  • 2
-1

Try using the explode function at the hyphen like this...

$exploded = explode('-', 'abc-def');

it will put abc and def into an array then you can store them in the corresponding places

f2 = $exploded[0]; <-- meaning abc
f3 = $exploded[1]; <-- meaning def
Daniel Morgan
  • 123
  • 1
  • 1
  • 8
  • He never mentioned PHP... In fact, he specifically says he wants to use MySQL directly. – Adam Plocher May 14 '13 at 22:20
  • yeah i need to do this in mysql CLI – pdgetrf May 14 '13 at 22:22
  • Then you clearly haven't read the MySQL manual... http://dev.mysql.com/doc/refman/5.1/en/string-functions.html – Daniel Morgan May 14 '13 at 22:25
  • have just figured it out: update mytable set f2=(SELECT SUBSTRING_INDEX(f1, '-', 1)) |||| update mytable set f3=(SELECT SUBSTRING_INDEX(f1, '-', -1)) – pdgetrf May 14 '13 at 22:37
  • @pdgetrf you should answer your own question then and mark it as answer. Good job. – Adam Plocher May 14 '13 at 22:49
  • @DanielMorgan actually I have. If you read the comments on your link there are plenty of suggestions how to split strings in MySQL. Regardless of if it's possible (which it clearly is), you can't just assume someones using a particular programming language. There are hundreds or thousands to choose from, and even then he could be using MySQL directly. – Adam Plocher May 14 '13 at 22:52
  • @AdamPlotcher I was talking to pdgetrf when I said he hasn't read the MySQL Manual, you can't just assume that everyone's comments are aiming towards you... and I never said it wasn't possible to do it, I said he hasn't read the manual because it tells you how to do it. – Daniel Morgan May 15 '13 at 13:56