0

I am currently using sql server 2014

I have a sql column which could contain the following values

user_email
--------------------------------------------
fred.bloggs@abc.co.uk
joe.bloggs@abc.co.uk
sally.bloggs@abc.co.uk;fred.bloggs@abc.co.uk

I need to select columns and build a where clause

so where user_email like '%sally.bloggs@abc.co.uk%' will select the 3rd row, but if I was to do user_email like '%lly.bloggs@abc.co.uk%' it would not select the 3rd row.

Hopefully this makes sense?

I can't seem to find a solution.

George Georgiou
  • 3
  • 1
  • 6
  • 1
    Not storing delimited data is the *real* solution here. Fix your design, fix the problem. – Thom A Apr 06 '20 at 12:39
  • Its a legacy app, I really don't have a choice. – George Georgiou Apr 06 '20 at 12:42
  • 1
    Legacy applications have to be updated at some point. What better time to start than now? – Thom A Apr 06 '20 at 12:44
  • 1
    `';' + user_email + ';' LIKE '%;lly.bloggs@abc.co.uk;%'` – MatBailie Apr 06 '20 at 12:45
  • I couldn't understand your statement *"if I was to do user_email like `'%lly.bloggs@abc.co.uk%'` it would not select the 3rd row"* - if you do that it WILL select the 3rd row? – Caius Jard Apr 06 '20 at 12:48
  • @CaiusJard - The Op wants it to Not match, because `lly` has a relation called `sally`, but they don't have the same email address, so they shouldn't match. *(The wild card matching being used to overcome the bad design of using `;` delimitered emails, but otherwise desiring exact matches only.)* – MatBailie Apr 06 '20 at 12:58

1 Answers1

0

In some cases the cost of improving the data structure is indeed higher than benefit it would yield, leading to the desire to use a workaround.

Just be careful, by using a workaround you accrue technical debt:
- More things to go wrong more easily in the future

For a quick win, just force the strings you're comparing to start and end with the delimiter being used...

';' + user_email + ';' LIKE '%;' + 'lly.bloggs@abc.co.uk' + ';%'
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • I'm not *really* sure why we needed another duplicate of "how to split a delimited string in SQL Server". :/ – Thom A Apr 06 '20 at 12:53
  • @larnu : The answer you linked was about splitting one row in to several rows, which is a heavy-weight solution to a light-weight problem. In order to be of help to the OP I believe that providing a useful light-weight approach to his personal scenario is of benefit. (feel free to close the question again, I'm not chasing rep) – MatBailie Apr 06 '20 at 12:56
  • Unfortunately I cannot lock this again, but this is does feel like a duplicate of this [answer](https://stackoverflow.com/a/33278978/2029983) or this [answer](https://stackoverflow.com/a/20525923/2029983). – Thom A Apr 06 '20 at 12:58
  • Ye.. Just concerned with all the coronatenation going on that that `... + 'lly.bloggs@abc.co.uk' + ...` would straight up become `... + '{loginNameTextBox.Text}' + ...` – Caius Jard Apr 06 '20 at 14:07
  • @CaiusJard - Do Not Edit My Answers – MatBailie Apr 06 '20 at 14:12