Solved

replace string elements from table - MySQL

Posted on 2011-03-10
12
377 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 51

Expert Comment

by:HainKurt
ID: 35097837
is it always 4 element?
0
 

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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 51

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 51

Accepted Solution

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now