-1

I checked both of these:

Delete part of a field in SQL

Deleting part of a string in MYSQL

but they're not quite what I need.

I've unfortunately inherited a table with a field full of comma-separated strings with no regard for format or order. It's ugly. Now I need to get rid of a particular part of a string:

So let's say I have a table as such:

    Column1   Column2
    1         XRR01,MMEX1,XFR44
    2         XRR02,MMEX1
    3         MMEX1,GH345,XFR45,CFA34
    4         NMM22,MMEX1,XFR44
    5         MMEX1
    6         XFR55

I want to do an UPDATE to find and delete any instances of MMEX1 from the Column2 field, and keep the other parts intact.

So then I would be left with:

    Column1   Column2
    1         XRR01,XFR44
    2         XRR02
    3         GH345,XFR45,CFA34
    4         NMM22,XFR44
    5         NULL (or blank, doesn't really matter)
    6         XFR55

Is this possible?

Stpete111
  • 3,109
  • 4
  • 34
  • 74
  • 1
    My advice it to take the table you inherited and expand it so each Column1 value has separate rows for each of the possible Column2 values. Yes, this likely means changing whatever application this table supports, but it will be worth it. I promise. And it will take your problem here and make it MUCH simpler. – Joel Coehoorn Jan 21 '18 at 03:31
  • Trust me I know, and I fully plan to do so when deadlines aren't closely looming (when does that ever happen??!) :) – Stpete111 Jan 21 '18 at 13:50

1 Answers1

1

Here is one way using Replace function 

select replace(replace(Column2,',MMEX1',''),'MMEX1,','') 
from Yourtable

replace(Column2,',MMEX1','') will remove the string in middle and last replace(Column2,'MMEX1,','') will remove the string at the start

Note : storing comma sepearated value in a column is always a pain when comes to data processing. Try and change the table structure

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172