In SQL, how do I coalesce the first value of 3 columns that is 10 characters long

I am trying to only select one column from 3 distinct phone number columns, and I would prefer to have the column with the longest phone number (some don’t have area codes). Currently, I have coalesced the 3 columns as some are also null, with a preference on cell phone.

Ex.

CREATE TABLE dbo.Persons
(
    first_name VARCHAR(100) NULL
   ,cell_number VARCHAR(20) NULL
   ,home_number VARCHAR(20) NULL
   ,work_number VARCHAR(20) NULL
);

INSERT INTO dbo.Persons
(
    first_name
   ,cell_number
   ,home_number
   ,work_number
)
VALUES
    ( 'Dave' -- first_name - varchar(100)
     ,'4567891' -- cell_number - varchar(20)
     ,'1234567891' -- home_number - varchar(20)
     ,NULL -- work_number - varchar(20)
    )
,
    ( 'Ron' -- first_name - varchar(100)
     ,'1234567891' -- cell_number - varchar(20)
     ,NULL -- home_number - varchar(20)
     ,NULL -- work_number - varchar(20)
    )
,
    ( 'Yitzhak' -- first_name - varchar(100)
     ,'123' -- cell_number - varchar(20)
     ,NULL -- home_number - varchar(20)
     ,'1234567891' -- work_number - varchar(20)
    );

What I’ve tried :

SELECT TOP 1
    COALESCE(cell_number, home_number, work_number)
FROM Persons ORDER BY LEN(cell_number),LEN(home_number),LEN(work_number) DESC;

I would prefer to just have the first number that is 10 characters long or the longest of the 3 columns, I can only have one column returned. Bonus points if it can keep a preference of cell_number , but not required.

@@version : Microsoft SQL Server 2019 (RTM) – 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 18363: ) (Hypervisor)

Answer

You can use a case expression. However, outer apply is simpler than a big case expression — and generalizes to more phone numbers much more easily:

select p.*, v.phone
from persons p outer apply
     (select top (1) v.phone
      from (values (cell_number, 1), (home_number, 2), (work_number, 2)
           ) v(phone, priority)
      where v.phone is not null
      order by len(v.phone) desc, priority
     ) v;

Advertisement

Answer