Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

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

0
tommarshallandrews
Asked:
tommarshallandrews
  • 5
  • 3
  • 3
  • +1
1 Solution
 
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
 
HainKurtSr. System AnalystCommented:
is it always 4 element?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now