DECLARE  @City TABLE (CityID int,CityName varchar(100),AlternateCityName1 varchar(100),AlternateCityName2 varchar(100))

INSERT INTO @City(CityID,CityName,AlternateCityName1,AlternateCityName2)
SELECT 1,'Cochin','Kochi','Ernakulam'
UNION ALL
SELECT 2,'Kollam','Quillon',NULL
UNION ALL
SELECT 3,'Mumbai','Bombay',NULL
UNION ALL
SELECT 4,'Chennai','Madras',NULL
UNION ALL
SELECT 5,'Kolkata','Calcutta',NULL

--SELECT * FROM @City


DECLARE @SearchString varchar(20) = 'k'
;WITH City_CTE(CityID,Name) AS (
SELECT 
CityID,
'Name' = CASE 
	WHEN CityName like (@SearchString + '%') THEN CityName
	WHEN AlternateCityName1 like (@SearchString + '%') THEN AlternateCityName1
	WHEN AlternateCityName2 like (@SearchString + '%') THEN AlternateCityName2
END	

FROM @City )
SELECT * FROM City_CTE WHERE Name IS NOT NULL 

drop table city
go
Create TABLE city(CityID int identity Primary key,CityName varchar(100),AlternateCityName1 varchar(100),AlternateCityName2 varchar(100))
go
Create index idxName on City(CityName)
go
Create index idxAlternate on City(AlternateCityName1) where AlternateCityName1 is not null
go
Create index idxAlternate2 on City(AlternateCityName2) where AlternateCityName2 is not null
go
INSERT INTO City(CityName,AlternateCityName1,AlternateCityName2)
select city,city,city from AdventureWorks2012.Person.Address
go
set statistics io on
go
Select * from City
where CityName like 'Mad%'
 or  AlternateCityName1 like 'Mad%'
 or  AlternateCityName2 like 'Mad%'

 go

 DECLARE @SearchString varchar(20) = 'mad'
;WITH City_CTE(CityID,Name) AS (
SELECT 
CityID,
'Name' = CASE 
	WHEN CityName like (@SearchString + '%') THEN CityName
	WHEN AlternateCityName1 like (@SearchString + '%') THEN AlternateCityName1
	WHEN AlternateCityName2 like (@SearchString + '%') THEN AlternateCityName2
END
FROM City )
SELECT * FROM City_CTE WHERE Name IS NOT NULL

SELECT S.CityId, C.Name 
FROM @City AS S
CROSS APPLY (VALUES (CityName),(AlternateCityName1),(AlternateCityName2)) C(Name) 
WHERE C.Name LIKE @SearchString + '%'