replace string elements from table - MySQL

I have a field in a table that contains a large number of CSVs.

I need the SQL to swap all the elements in the string with their equivalent sourced from another table - see attachmnent example.

any help gratefull received..
old string = a,b,c,d
new string = 1,2,3,4


translation table

old    new
a      1
b      2
c      3
d      4

Open in new window

tommarshallandrewsAsked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
use concat(str1,str2...) function

update myTable set csv2=concat(col1,',',col2,','...',',col10)

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat
0
 
mayankagarwalCommented:
create a view on both the tables, or you can do the same with the help of a join also
0
 
mayankagarwalCommented:
sorry it was a wrong solution, you can use split function based on comma
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
HainKurtSr. System AnalystCommented:
is it always 4 element?
0
 
tommarshallandrewsAuthor Commented:
no its not. any number up to about 10
0
 
tommarshallandrewsAuthor Commented:
sorry not sure how i would use a split function??
0
 
mayankagarwalCommented:
0
 
Doug WaltonDatabase AdministratorCommented:
This might also be helpful:
http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

He provides a mysql function you can implement.
0
 
HainKurtSr. System AnalystCommented:
what about this

create 10 more columns
col1, col2...col10

run this

splitting csv into multiple columns

update mytable set col1=replace(substring(substring_index(csv, ',', 1), length(substring_index(csv, ',', 1 - 1)) + 1), ',', '')
update mytable set col2=replace(substring(substring_index(csv, ',', 2), length(substring_index(csv, ',', 2 - 1)) + 1), ',', '')
..
update mytable set col2=replace(substring(substring_index(csv, ',', 10), length(substring_index(csv, ',', 10 - 1)) + 1), ',', '')

then we can use simple updates to translate col1,..,col10

update mytable set col1=(select new from translatetable where old=col1)
..
update mytable set col10=(select new from translatetable where old=col10)

then we can join all 10 columns into 1 and put it into a new or old column, csv

update myTable set csv2=col1 +','+ col2 +','+ ..+','+ col10
0
 
tommarshallandrewsAuthor Commented:
Thanks Guys,

I'll give it a go and see what happens.
0
 
tommarshallandrewsAuthor Commented:
works great!

only hitch is that the last stage is adding the numerical numbers together rather than returning a string.
0
 
tommarshallandrewsAuthor Commented:
thanks! that's great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.