CODEWITHSUNDEEP
mysqlsqlsql-server-2008sql-server-2008-r2
Arunesh
Arunesh

Reputation: 297

How to use conditional operator in sql

What I am trying to retrieve is the contact number. I have a relation called BusinessDetails and the fields are Name,Description, PhoneNum, MobileNum. Case of contact number are as follows:

  1. I can have both MobileNum and PhoneNum.
  2. I can have any one of them
  3. I can't have both of them

I am trying this piece of sql select statement and it is quite good when there are both number. but in the 2 and 3 case there is unnecessary comma associated with it. My sql is

   SELECT Business.PhoneNum+','+Business.MobileNum AS ContactNumber 
     FROM dbo.BusinessDetails AS Business

Can some body help me out for removing comma in 2nd and 3rd case?

Upvotes: 1

Views: 63

Answers (2)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186803

You can use case (general solution which works for almost all DBMS's):

select case
         when PhoneNum is null then -- MobileNum only
           MobileNum
         when MobileNum is null then -- PhoneNum only
           PhoneNum
         when (PhoneNum is not null) and (MobileNum is not null) then -- Both
           MobileNum || ',' || PhoneNum
         else -- Neither MobileNum no PhoneNum  
           'No Phone'
       end as ContactNumber      
  from dbo.BusinessDetails as Business

Upvotes: 1

Aziz Shaikh
Aziz Shaikh

Reputation: 16534

Use CONCAT_WS(), like this:

select CONCAT_WS(',', Business.PhoneNum, Business.MobileNum) 
from dbo.BusinessDetails as Business

Upvotes: 2

Related Questions