Rather than using a LIKE could you use CHARINDEX - something like
declare @splitresults table (dataitem varchar(max))
insert into @splitresults values
('vessel'), ('ship'), ('container')
declare @project table(id int identity(1,1), textitem varchar(max))
insert into @project values
('this vessel should be found'),
('this ship is a vessel'),
('this container is a box'),
('should not find me')
SELECT *
FROM @project A
INNER JOIN @splitresults B ON CHARINDEX(B.dataitem, A.textitem) > 0
This gave the following results
id textitem dataitem
1 this vessel should be found vessel
2 this ship is a vessel vessel
2 this ship is a vessel ship
3 this container is a box container
You would need to join on all the relevant columns (or use unions) Won't be particularly performant I guess.
For SQL 2014 see this thread for a split function
How to split strings in sql server[
^]
EDIT: I've just spotted another problem see
WHERE 1 = 1
AND ProjectNo LIKE '%' + @searchtext + '%'
OR MainProjectNo LIKE '%' + @searchtext + '%'
OR CustomerNo LIKE '%' + @searchtext + '%'
You are mixing AND and OR try
WHERE 1 = 1
AND
(
ProjectNo LIKE '%' + @searchtext + '%'
OR MainProjectNo LIKE '%' + @searchtext + '%'
OR CustomerNo LIKE '%' + @searchtext + '%'
. . .
)
EDIT 2 - This is what I meant by options for checking the other columns. The 2nd approach has the advantage of removing duplicate results
SELECT A.*
FROM @project A
INNER JOIN @splitresults B ON CHARINDEX(B.dataitem, A.textitem1) > 0 OR CHARINDEX(B.dataitem, A.textitem2) > 0
SELECT A.*
FROM @project A
INNER JOIN @splitresults B ON CHARINDEX(B.dataitem, A.textitem1) > 0
UNION
SELECT A.*
FROM @project A
INNER JOIN @splitresults B ON CHARINDEX(B.dataitem, A.textitem2) > 0
EDIT after OP comments
I misunderstood the requirement -
all of the search items must appear in a row for it to be included.
I changed my test data to have additional columns:
declare @project table(id int identity(1,1), textitem varchar(max), textitem2 varchar(max), textitem3 varchar(max))
insert into @project values
('this vessel should be found','ship','container'),
('this ship is a vessel','1','ship'),
('this container is a box','vessel','2'),
('should not find me at all','1','2')
I also added a count of how many search items we have
declare @searchitems int = (SELECT COUNT(*) FROM @splitresults)
I dropped a list of potential candidates into a temporary table
SELECT DISTINCT A.id, A.textitem + A.textitem2 + A.textitem3 as searchstring, B.dataitem
INTO #res1
FROM @project A
INNER JOIN @splitresults B ON CHARINDEX(B.dataitem, A.textitem) > 0 OR CHARINDEX(B.dataitem, A.textitem2) > 0 OR CHARINDEX(B.dataitem, A.textitem3) > 0
The key premise is that any column that can be searched forms the column
[searchstring]
in a fixed order and each of those columns must also feature in the ON clause.
That temp table now contains 0 to n rows for each of the projects where n will be the number of search items. So just extract the rows from @project for any id that appears in #res n times..
select * from @project WHERE id IN
(
select id
FROM #res1
group by id
having COUNT(*) = @searchitems
)
Not the nicest, but it seems to work - what do they say about brute force and ignorance :-)
I also tried a recursive CTE but just couldn't get my head around it.