-1

The following field of a table:

AttorneyEmail(varchar(150), null)

Can have more than one email address, but has this email address in all "helpdesk@dns.org".

I have the following within the Where clause to not include in results:

and aa.AttorneyEmail NOT LIKE ('helpdesk%')

But it still does.

Any help would be appreciated.

Regards,

Alex K.
  • 171,639
  • 30
  • 264
  • 288
jr7138
  • 13
  • 5
  • 4
    Multiple addresses in a single field (yuk!)? - `and aa.AttorneyEmail NOT LIKE ('%helpdesk%')` ? – Alex K. Oct 09 '14 at 15:18
  • `VARCHAR(150)` is potentially not long enough for a single email address ([maximum length of 254 characters](http://stackoverflow.com/q/386294/1048425)), let alone multiple email address! As @AlexK. has said, multiple email addresses in a single field is not a good idea - you may wish to read up on "one to many relationships". – GarethD Oct 09 '14 at 15:31
  • Storing multiple values in a single tuple violates 1NF. Normalize your data and your life will be a lot easier. If you have good ddl the dml is really easy. – Sean Lange Oct 09 '14 at 15:35
  • And if really **every** row has the helpdesk address embedded (and is supposed to do) then what's the point? Whatever software consumes the data could as easily add the helpdesk address to the values it retrieves. When needed. – John Bollinger Oct 09 '14 at 15:43
  • I agree with Alex K, but I came into this role with tables already in place and you would not believe what it would take to correct. Yes, John Bollinger it is ugly. – jr7138 Oct 09 '14 at 16:08

1 Answers1

0

What you're describing doesn't make much sense. If every row contains the helpdesk address within its AttorneyEmail value (as you say) then a WHERE predicate such as you are trying to use (spelled as @AlexK demonstrates) would exclude all rows. (Also, such a DB structure is pretty ugly.)

In that case, if the point is to strip the helpdesk address from the column value in your results, then you need to do so in the selection list, something like

SELECT REPLACE(aa.AttorneyEmail, 'helpdesk@dns.org', '') AS AttorneyEmail,
...

You may need to adjust that to remove excess delimiters; I can't suggest exactly how because I don't know how you are structuring the values.

John Bollinger
  • 160,171
  • 8
  • 81
  • 157
  • John: this worked! REPLACE(aa.AttorneyEmail, ',ais.helpdesk@dns.org', '') as AttEmail Thanks – jr7138 Oct 10 '14 at 19:21