Solved

replace string elements from table - MySQL

Posted on 2011-03-10
12
380 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 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 35097837
is it always 4 element?
0
Upcoming Webinar: Securing your MySQL/MariaDB data

Join Percona’s Chief Evangelist, Colin Charles as he presents Securing your MySQL®/MariaDB® data on Tuesday, July 11, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 

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 55

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 55

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

707 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