SQL Query To Return Phone Number

Question

I have a table in SQL Server hat has:

FirstName, LastName, MiddleName, CellNumber, HomeNumber
John Smith M 111-111-1111, 222-222-2222

The person can have both CellNumber and HomeNumber or can have CellNumber but no HomeNumber or can have HomeNumber and no CellNumber.

How do i write a query that will always return CellNumber if exists and HomeNumber only if CellNumber is NULL or blank.

SQL query to produce following results:

FirstName, LastName, MiddleName, NumberToUse, PhoneType
John Smith M 111-111-1111 CellNumber

Thanks for your help.

Possible duplicate of [How do I check if a Sql server string is null or empty](https://stackoverflow.com/questions/334108/how-do-i-check-if-a-sql-server-string-is-null-or-empty) — stefancarlton, Jan 25 '18 at 03:19

Gordon Linoff · Answer 1 · 2018-01-25T03:50:05.467

0

You use case:

select . . .,
       (case when cellNumber is not null or cellNumber <> '' then cellNumber else homeNumber en) as NumberToUse,
       (case when cellNumber is not null  or cellNumber <> '' then 'Cell' else 'Home' end) as PhoneType
from t;

If blank could include spaces, you can remove the spaces (for the comparison) using replace() or ltrim().

The comparison to for is not null is, strictly speaking, not necessary. But it makes the query's intention more obvious.

edited Jan 25 '18 at 03:50

answered Jan 25 '18 at 03:18

Gordon Linoff

1,242,037
58
646
786

1

That will return blank cellNumbers. Also if cellNumber is not null or cellNumber = '' will return 'Cell'? C'mon Gordon. Oh man, I corrected Gordon Linoff. :) – Gordon Bell Jan 25 '18 at 03:48
That's better. :) – Gordon Bell Jan 25 '18 at 03:51
@GordonBell . . . When I first read the query, I missed the part about blanks. I didn't do a very good job on the edit the first time. – Gordon Linoff Jan 25 '18 at 03:52

Bae · Answer 2 · 2018-01-25T03:29:08.937

0

Just one case when statement to first check if CellNumber is available, else fallback to HomeNumber.

SELECT 
    FirstName, 
    LastName, 
    MiddleName,
    CASE WHEN  
        CellNumber IS NOT NULL THEN CellNumber
    ELSE 
        HomeNumber 
    END AS NumberToUse,
    PhoneType
FROM
    TableName

edited Jan 25 '18 at 03:29

answered Jan 25 '18 at 03:22

Bae

88
6

score 0 · Answer 3 · answered Jan 25 '18 at 03:25

0

coalesce is your friend here.. it returns the first non null values in the list.. so you don't have to do a whole heap of case statements

 select coalesce(cellnumber,HomeNumber) from yourtable

answered Jan 25 '18 at 03:25

Harry

2,636
1
17
29

score 0 · Accepted Answer · answered Jan 25 '18 at 03:26

0

Another option is Coalesce() in concert with NullIf()

Example

Select FirstName 
      ,LastName
      ,MiddleName 
      ,DefaultPhone = coalesce(NullIf(CellNumber,'')+' Cell',NullIf(HomeNumber,'')+' Home')
 from YourTable

answered Jan 25 '18 at 03:26

John Cappelletti

79,615
7
44
66

Gordon Bell · Answer 5 · 2018-01-25T05:24:06.437

0

select FirstName, LastName, MiddleName,
case when isnull(CellNumber, '') = '' then HomeNumber else CellNumber end as NumberToUse,
case when isnull(CellNumber, '') = '' then 'HomeNumber' else 'CellNumber' end as PhoneType
from ...

edited Jan 25 '18 at 05:24

answered Jan 25 '18 at 03:44

Gordon Bell

13,337
3
45
64

1

Please consider adding some explanation to your answer. – Sunil Jan 25 '18 at 04:22

SQL Query To Return Phone Number

5 Answers5