0

In my table, email recipients list is stored as a single column comma separated value. And I have another table with email addresses to be blocked as a single column value. Now I want to remove blocked emails from the first table and update only allowed emails in the first table column value.

I already have function to return comma separated table value. Is there any default function or easiest way to achieve this?

EX:

Select EmailAddress From TableA
'[email protected],[email protected],[email protected],[email protected],[email protected]'

Select EmailAddress From TableB
'abc@gmail,mno@gmail'

Expected result after update:

Select EmailAddress From TableA
'[email protected],[email protected],[email protected]'
Tech Learner
  • 1,227
  • 6
  • 24
  • 59
  • 1
    You can do this by converting the whole thing to XML, splitting, `EXCEPT`ing, then using XML again to concatenate stuff -- but you *really don't want to*. You *want* to have the email addresses stored into separate rows. You say you "have [a] function to return [a] comma separate table value" -- is that operating on rows, by any chance? – Jeroen Mostert Jul 30 '18 at 12:42
  • 2
    Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** – Zohar Peled Jul 30 '18 at 12:47
  • 1
    why is `TableB` comma delimited as well? it would make more sense for a row for each blocked email address – WhatsThePoint Jul 30 '18 at 12:52
  • @WhatsThePoint - Can I make it simple, if TableB has row for each bloked email address. – Tech Learner Jul 30 '18 at 13:24
  • @Ask_SO that doesn't answer my question – WhatsThePoint Jul 30 '18 at 13:29

0 Answers0