I have a similar issue to the question in:
ORDER BY using custom priorities for text columns
...except that I want to only get the data out if I have certain values. To clarify, we have a table that stores both email addresses and phone numbers in the same column.
I cannot change this structure without breaking a critical system that I do not have source code access to. I need to be able to retrieve only email addresses, and have the query show NULL
when there is no email address.
The table structure is:
- ID (Int)
- Description Code (Int) (can use same values for both emails and phone numbers, nothing distinguishing here)
- Number (varchar)
Right now, similar to the question above, I have my SQL as:
order by case
when con2.NUMBER like '%@ourdomain.com' then 1
when con2.NUMBER like '%@%' then 2
else 3
end
I would like to not show phone numbers in this output. Is there an easy adjustment to my code to facilitate this?
Example
Records for Sally
ID Type Number
1 1011 (215)555-1213
2 1011 (215)555-1312
3 1012 sally@ourdomain.com
4 1013 sallyf@gmail.com
Records for Frank
ID Type Number
5 1013 frankiebaby@yahoo.com
6 1013 franks@ibm.com
Records for Julius
ID Type Number
7 1010 555-123-9876
8 1011 425-369-1470
Output would return sally@ourdomain.com for Sally and frankiebaby@yahoo.com for Frank, but NULL
for Julius.
I need to prioritize one domain over any others, so addresses in our own domain would show up if it's available, if not, then google, hotmail, or whatever else comes secondary.