SQL Query - Match each word

Riddhi Valecha

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?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
Accepted 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,@StrData,0);

    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,@start,LEN(@StrData)-@start+1)

RETURN
END
And you can call this function as
SELECT * FROM dbo.SimpleSplitFunction('t1,t2,t3,t4,t5',',')
+1
Tapan Patel9y ago
So, users will always provide input(s) that is comma seperated right? Also, what is the SQL server version?
0
Riddhi Valecha9y ago
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..
0
Tapan Patel9y ago
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,%’
0