?
Solved

replace string elements from table - MySQL

Posted on 2011-03-10
12
Medium Priority
?
385 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:tommarshallandrews
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35097820
create a view on both the tables, or you can do the same with the help of a join also
0
 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35097831
sorry it was a wrong solution, you can use split function based on comma
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35097837
is it always 4 element?
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 

Author Comment

by:tommarshallandrews
ID: 35097866
no its not. any number up to about 10
0
 

Author Comment

by:tommarshallandrews
ID: 35097879
sorry not sure how i would use a split function??
0
 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35097912
0
 
LVL 5

Expert Comment

by:Doug Walton
ID: 35097966
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 35098046
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
 

Author Comment

by:tommarshallandrews
ID: 35107402
Thanks Guys,

I'll give it a go and see what happens.
0
 

Author Comment

by:tommarshallandrews
ID: 35107506
works great!

only hitch is that the last stage is adding the numerical numbers together rather than returning a string.
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 1000 total points
ID: 35110054
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
 

Author Comment

by:tommarshallandrews
ID: 35138428
thanks! that's great!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question