?
Solved

replace string elements from table - MySQL

Posted on 2011-03-10
12
Medium Priority
?
382 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 58

Expert Comment

by:HainKurt
ID: 35097837
is it always 4 element?
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 4

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 58

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 58

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
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

765 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