Solved

replace string elements from table - MySQL

Posted on 2011-03-10
12
379 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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35097837
is it always 4 element?
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 53

Expert Comment

by:Huseyin KAHRAMAN
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 53

Accepted Solution

by:
Huseyin KAHRAMAN earned 250 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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