Order by custom filter without certain data

Question

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.

Answer 1

This will get your result set, though I did add the Name field for clarity. The common table expression (cte) with ROW_NUMBER() will get the email priority. Then I left joined that back to the base table to return the names and a NULL for Julius.

CREATE TABLE #emailOrPhone (ID INT IDENTITY(1,1), Type INT, Number VARCHAR(50), Name VARCHAR(20))

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'(215)555-1213','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'(215)555-1312','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1012,'sally@ourdomain.com','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'sallyf@gmail.com','Sally')

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'frankiebaby@yahoo.com','Frank')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'franks@ibm.com','Frank')

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1010,'555-123-9876','Julius')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'425-369-1470','Julius')

;WITH cte AS
(
SELECT Name, Number
, CASE WHEN Number like '%@ourdomain.com' THEN 1 ELSE 2 END AS 'emailPriority'
, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) as rn
FROM #emailOrPhone
WHERE Number like '%@%'
)

SELECT DISTINCT Names.Name, Emails.Number
FROM #emailOrPhone as Names
LEFT JOIN cte as Emails on Names.Name = Emails.Name and Emails.rn = 1
ORDER BY Names.Name, Emails.Number

DROP TABLE #emailOrPhone

Answer 2

Your requirement is not very clear but I believe you need to show NULLs when there are phone numbers and otherwise Email IDs in the query result. Let me know how this works:

SELECT 
ID, 
[Description Code], 
CASE WHEN NUMBER LIKE '%@%' THEN NUMBER ELSE NULL END DerivedEmailColumn
FROM YourTableName
ORDER BY DerivedEmailColumn