CODEWITHSUNDEEP
sql-server
DragonZelda
DragonZelda

Reputation: 168

SQL find the column which match the parameter

I have a SQL to determine when a call come in. If the number match one of the address book number, then it will display the result. The logic is similar as following:

select * from  mstCustomerContact
where CC_IsDelete<>1
and (
Convert(varchar(100),RIGHT(CC_Ext1, 4)) = @Phone
or Convert(varchar(100),RIGHT(CC_Ext2, 4)) = @Phone
or Convert(varchar(100),CC_MobileNo) = @Phone 
or Convert(varchar(100),CC_ClinicNo) = @Phone 
or convert(varchar(100),CC_HomeNo) = @Phone 
or Convert(varchar(100),CC_Ext1) = @Phone 
or Convert(varchar(100),CC_Ext2) = @Phone
or Convert(varchar(100),CC_Ext3) = @Phone
or Convert(varchar(100),CC_Ext4) = @Phone
or Convert(varchar(100),CC_Ext5) = @Phone)

I can get the record, but I don't know the Phone match to which column. Is it possible to retrieve which column it match? Thanks.

Upvotes: 1

Views: 49

Answers (2)

slartidan
slartidan

Reputation: 21586

If possible refactor your database scheme.

Currently you are storing similar contents in separate columns. This causes your problem.

Table Person
Id | Name | PhoneNumber1 | PhoneNumber2 | etc
1  | Max  | 01213        | 8172         |

I would recommend to create an additional "Numbers Table".

Table Person
Id | Name
1  | Max

Table Number
PersonId | Type         | Number
1        | PhoneNumber1 | 01213
1        | PhoneNumber2 | 8172

This makes queries simpler:

SELECT Id FROM Person
INNER JOIN Number ON Id = PersonId
WHERE Number = @input

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460158

Repeat them in the SELECT, you can use CASE:

SELECT
    cc_ext1_match = CASE WHEN CONVERT(VARCHAR(100), RIGHT(cc_ext1, 4)) = @Phone 
                    THEN 1 ELSE 0 END,
    cc_ext2_match = CASE WHEN CONVERT(VARCHAR(100), RIGHT(cc_ext2, 4)) = @Phone 
                    THEN 1 ELSE 0 END,
    cc_mobileno_match = CASE WHEN CONVERT(VARCHAR(100), RIGHT(cc_mobileno, 4)) = @Phone 
                        THEN 1 ELSE 0 END,
    ....
    , cc.* 
FROM   mstcustomercontact cc
WHERE  cc_isdelete <> 1 
       AND ( CONVERT(VARCHAR(100), RIGHT(cc_ext1, 4)) = @Phone 
              OR CONVERT(VARCHAR(100), RIGHT(cc_ext2, 4)) = @Phone 
              OR CONVERT(VARCHAR(100), cc_mobileno) = @Phone 
              OR CONVERT(VARCHAR(100), cc_clinicno) = @Phone 
              OR CONVERT(VARCHAR(100), cc_homeno) = @Phone 
              OR CONVERT(VARCHAR(100), cc_ext1) = @Phone 
              OR CONVERT(VARCHAR(100), cc_ext2) = @Phone 
              OR CONVERT(VARCHAR(100), cc_ext3) = @Phone 
              OR CONVERT(VARCHAR(100), cc_ext4) = @Phone 
              OR CONVERT(VARCHAR(100), cc_ext5) = @Phone )

Upvotes: 1

Related Questions