I want to get the record with matching phone number. There are total 3 columns that saves phone numbers. Work, home and cellphone. I am writing a query to check if the phone number matches with any of the 3 columns.
declare @phNo nvarchar(15) = 'xxxxxxxxxx' select HomePhone, cellphone, workphone, * from Contact_Table where @phNo in (HomePhone, cellphone, workphone)
This query is working fine, but I also want the name of the column which matches with the phone number. How can I get the matching column’s name?
For example if the phone number matches with ‘homehpone’ then the result set should return ‘homephone’
Advertisement
Answer
This can be done with CASE:
DECLARE @phNo NVARCHAR(15) = 'xxxxxxxxxx'; SELECT homephone,cellphone,workphone, *, CASE WHEN @phNo = homephone THEN 'homephone' WHEN @phNo = cellphone then 'cellphone' WHEN @phNo = workphone THEN 'workphone' END AS PhoneMatch FROM Contact_Table WHERE @phNo IN (HomePhone,cellphone,workphone);