SQL – Search ‘Address’ Where Contains string parameter

I have a stored procedure which locates the specific address.

Fields

• City
• Province
• Zip code

Records

• City : Manila, Makati, Cebu City, Kawit
• Province : NCR, NCR, CEBU, CAVITE
• Zip Code: 1111, 2222, 3333, 4444

My parameter only accepts 1 full string

Sample:

Declare @param as nvarchar (50)
SET @param = 'Man NC'
--query where condition like @param

The result should be Manila, NCR 111. I already tried this query. But obviously not correct because parameter contains city and province.

SELECT c.CityName, p.ProvinceName, c.Zipcode 
FROM City c (NOLOCK)
JOIN Province p (NOLOCK) ON c.ProvinceCode = p.ProvinceCode
WHERE c.CityName like '%' + @param + '%' 
OR p.ProvinceName like '%' + @param + '%' 
OR (c.CityName + ' ' + p.ProvinceName) like '%' + @param + '%'

I’ve seen some post here in stackoverflow suggesting using FULL-TEXT SEARCH. But how to do this without using the said function?

Answer

You’re allowed to concatenate your fields before you like them. You’re also allowed to edit your like string:

SELECT c.CityName, p.ProvinceName, c.Zipcode 
FROM City c (NOLOCK)
JOIN Province p (NOLOCK) ON c.ProvinceCode = p.ProvinceCode
WHERE CONCAT(c.CityName, ",", p.ProvinceName) like CONCAT('%', REPLACE(@param, ' ', '%'), '%')

Here I CONCAT the city and the province together and I turn your “Man NC” into “%Man%NC%” which will find the concatenated value

If you will feed queries in like “NC Man” you could simply have an OR with a CONCAT that concatenates the province then city. You could get a lot more involved, splitting the value etc; it really depends on what you specify (and you haven’t put a lot of spec into your question)

If you’re going to be doing a lot of queries, it would be better to nail down the variations of @param and set some rules so that things can be better indexed. An example rule might be: @param must be a single word or two words, and the words must represent the start of a city name or a province name or a city name and a province name.

With rules like that you can split on space, CONCAT a % onto just the end of the words, and search the columns appropriately- possibly with a union rather than an or (can be faster)

Edit: version without CONCAT function

WHERE (c.CityName + "," + p.ProvinceName) like ('%' + REPLACE(@param, ' ', '%') + '%')