CODEWITHSUNDEEP
sqlsql-server
jymskrl
jymskrl

Reputation: 83

SQL - Search 'Address' Where Contains string parameter

I have a stored procedure which locates the specific address.

Fields

Records

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?

Upvotes: 1

Views: 683

Answers (2)

Caius Jard
Caius Jard

Reputation: 74680

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, ' ', '%') + '%')

Upvotes: 1

Red Devil
Red Devil

Reputation: 2403

I am not exactly sure if this is what you want.

Declare @city table (city varchar(50))

insert into @city values ('Manila'),('Makati'),('Cebu City'),('Kawit')

Declare @province table (Province varchar(50),zip int)

insert into @province values ('NCR',1111),('NCR',2222),('CEBU',3333),('CAVITE','4444')

select top 1 city + ', ' + Province + cast(zip as varchar) as name from @city c cross join @province d  where city like 'Man%' and Province like 'NC%'

Upvotes: 0

Related Questions