SQL Query - Match each word

Question

Table – tbl_locations

ID	startlocation	endlocation
1	Ghansoli,Sector-5, Navi Mumbai	Thane
2	Palava, Navi Mumbai	Airoli, Sector - 10
3	Kalyan, Sector-15	Cabin-3A, Capgemini, Airoli
4	Navi Mumbai	Knowledge Park, Airoli, Navi Mumbai
5	Ambernath, Navi Mumbai	Airoli

User Input – Cabin-3A, Airoli

Query-

Select * from tbl_locations where startlocation like ‘%Cabin-3A %’ or startlocation like ‘%Airoli%’ or endlocation like ‘%Airoli%’or endlocation like ‘%Cabin-3A %’

Steps –

1.There are 2 words – Cabin-3A and Airoli.

I need to match both the words with two columns of the table.

How do I get the output?

Tapan Patel · Answer

you can use split function if you are using SQL 2016+ or else you can use some basic split function as below. it is not perfect but try improving it by adding some trims for empty characters/spaces. CREATE FUNCTION dbo.SimpleSplitFunction ( @StrData varchar ( MAX ) @SplitChar varchar (5) ) RETURNS @tbl TABLE (test VARCHAR (100)) AS BEGIN DECLARE @start INT = 0; DECLARE @ end INT =CHARINDEX(@SplitChar@StrData0); WHILE (@start end ) BEGIN INSERT INTO @tbl SELECT SUBSTRING (@StrData@start@ end -@start) SET @start = @ end +1; SET @ end = CHARINDEX(@SplitChar@StrData@start) END INSERT INTO @tbl SELECT SUBSTRING (@StrData@startLEN(@StrData)-@start+1) RETURN END And you can call this function as SELECT * FROM dbo.SimpleSplitFunction( 't1t2t3t4t5'  '' )

Tapan Patel · Answer

So users will always provide input(s) that is comma seperated right? Also what is the SQL server version?

Riddhi Valecha · Answer

Hi.. Thanks for this answer... But... The sentence - Cabin-3A Airoli - will be from the user. i.e. From front-end user will enter such inputs ... In the backend I need to seprate words and match each word... This is difficult.. Please guide..

Tapan Patel · Answer

If you want to match both the names on each columns then try with orders. It looks bit weird but these words can be in the string or at the end of the strings hence I have added multiple or conditions with little difference. Select * from tbl_locations where startlocation like ‘%Cabin-3A%Airoli’ or startlocation like ‘%Cabin-3A%Airoli%’ or startlocation like ‘%Airoli%Cabin-3A’ or startlocation like ‘%Airoli%Cabin-3A%’ or endlocation like ‘%Cabin-3A%Airoli’ or endlocation like ‘%Cabin-3A%Airoli%’ or endlocation like ‘%Airoli%Cabin-3A’ or endlocation like ‘%Airoli%Cabin-3A%’