[SOLVED] Removing part of a record?

[woolyg]

Hi all,

 

I have a field that contains the email addresses of all subscribers to a topic. Like this

 

|-----------|------|

| Topic ID  | Email |

|-----------|------|

|    23      | mail1@mail.net, mail2@mail.net, mail3@mail.net|

|-----------|------|

|    24      | mail4@mail.net, mail5@mail.net, mail6@mail.net|

 

 

What syntax would I use to remove only mail5@mail.net from the Email field of Topic ID 24, and keep the other info in the field?

 

All help appreciated,

Woolyg.

 

[mezise]

Hi Woolyg,

this query removes email from any position in Email field:

 

UPDATE `Your Table` SET `Email` = TRIM(BOTH ", " FROM REPLACE(REPLACE(`Email`,
  "mail5@mail.net",
  ""), ", , ", ", "))
WHERE `Topic ID` = 24;

 

If you need a little explanation, first function (TRIM) removes unnecessary separator when an email is removed from the beginning or from the end of a list, second function (REPLACE) removes unnecessary separator when an email is removed from the middle of a list, third function (REPLACE) removes given email address.

 

Michal

[woolyg]

Michal,

 

That is one beautiful piece of code! Thank you so much - I wouldn't have known how to do this prior to now. I'll mark this one down for my records..

 

Have yourself a great day.

Woolyg!

[woolyg]

Michal,

 

I'm implementing exactly the code you set out, but what I'm finding when an email is removed from the middle of a list, it's removing the comma straight after it, which is fine, but it's also removing the comma at the very end of the list, which is kinda fooling with my population coding. Is there a way to remove only the mail & the comma, without touching anything else?

 

eg

 

mail5@mail.net,

 

 

- Cheers,

Woolyg

[mezise]

Woolyg,

 

removing query has been written to conform to your example data and its separator ", ". If you changed your format and Email value looks like:

|mail4@mail.net, mail5@mail.net, mail6@mail.net,|

 

removing query will look like:

UPDATE `Your Table` SET `Email` = TRIM(LEADING " " FROM TRIM(LEADING "," FROM TRIM(TRAILING " ," FROM REPLACE(REPLACE(`Email`,
  "mail6@mail.net",
  ""), ", , ", ", "))))
WHERE `Topic ID` = 24;

 

But remember this new format is not very consistent, you have ", " separator between emails and "," at the end.

 

If you are interested, I would chose one of these formats:

Easier to read from

|mail4@mail.net,mail5@mail.net,mail6@mail.net|

or

Easier to write to

|mail4@mail.net,mail5@mail.net,mail6@mail.net,|

 

Try to play with TRIM, BOTH, LEADING, TRAILING, REPLACE.

 

Michal

[woolyg]

Cool,

 

Thanks Michal - I'll go about learning now...- woolyg.

[fenway]

Yes, that's fancy, but would be entirely unnecessarily if the table were normalized properly....