Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
My friends,

on a SQL Server 2014 I have a search stored procedure. In current case it checks for %searchterm% on several fields. If it matches it return data.
That is for most cases ok, but what is someone looking for 'b a' in stead of 'a b'? The user will not see any result whilst there are. How can I perform a search with unknown order of searchkeys?

I have created a SplitString function (the sql server 2016 has one, but 2014 fails) to have rows which holds one searchterm per row.
How can I use these rows in a like so I can return results based on random order of given searchterms by a user

What I have tried:

SQL
DECLARE @searchtext NVARCHAR(1000)= 'vessel ship container';
SELECT pv.[Id], 
       [ProjectNo], 
       [MainProjectNo], 
       [ProjectDescription], 
       [CustomerNo], 
       [CustomerName], 
       [Status],
       CASE
           WHEN [ProjectTypeCode] = 'NULL'
           THEN NULL
           ELSE [ProjectTypeCode]
       END AS [ProjectTypeCode], 
       [ManagerName],
       CASE
           WHEN [EngineerName] = 'NULL'
           THEN NULL
           ELSE [EngineerName]
       END AS [EngineerName],
       
FROM [dbo].[IProjectView] AS pv
     CROSS APPLY dbo.SplitString(@searchtext, ' ') as res
WHERE 1 = 1
      AND ProjectNo LIKE '%' + @searchtext + '%'
      OR MainProjectNo LIKE '%' + @searchtext + '%'
      OR CustomerNo LIKE '%' + @searchtext + '%'
      OR CustomerName LIKE '%' + @searchtext + '%'
      OR [Status] LIKE '%' + @searchtext + '%'
      OR ProjectTypeCode LIKE '%' + @searchtext + '%'
      OR ManagerName in (select [Value] from dbo.SplitString(@searchtext, ' ')) -- LIKE '%' + @searchtext + '%'
      OR EngineerName LIKE '%' + @searchtext + '%'
      OR ProjectDescription LIKE '%' + @searchtext + '%'
ORDER BY ProjectNo;


The SplitString function returns:
%vessel%
%ship%
%container%
Posted
Updated 11-Jun-20 3:09am
Comments
W Balboos, GHB 11-Jun-20 8:38am    
The splitstring results, in a series LIKE .... AND LIKE ... seem like they'd do the trick. AND's force all the matches but anywhere in a string would do it for each.

You need to build the QUERY dynamically based upon the number of elements in your split-string results (or should, it' makes a better exercise)
Herman<T>.Instance 11-Jun-20 8:52am    
dynamically, you mean by CTE?
W Balboos, GHB 11-Jun-20 9:00am    
No - I'm very simple about this. You can build a query string in a loop so it handles each of N components.

Another way: create a #temp table with on substring's match results. Then deleted from that table all that do not contain the other substrings.

That's really the same net result as the bunch of AND'ed LIKE clauses.

Nothing mysterious - straight up direct. If I were doing this I'd be building my string in php (or C or C++) and sending it to the server already built.

1 solution

Rather than using a LIKE could you use CHARINDEX - something like
SQL
declare @splitresults table (dataitem varchar(max))
insert into @splitresults values
('vessel'), ('ship'), ('container') 
-- Code above just spoofing the results of a split function.

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')
-- The table above is just some dummy data to search

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
SQL
WHERE 1 = 1
      AND ProjectNo LIKE '%' + @searchtext + '%'
      OR MainProjectNo LIKE '%' + @searchtext + '%'
      OR CustomerNo LIKE '%' + @searchtext + '%'
You are mixing AND and OR try
SQL
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
SQL
--Approach 1 - multiple conditions in ON clause
SELECT A.*
FROM @project A
INNER JOIN @splitresults B ON CHARINDEX(B.dataitem, A.textitem1) > 0 OR CHARINDEX(B.dataitem, A.textitem2) > 0

-- Approach 2 UNION multiple queries - one query per column to check
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:
SQL
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
SQL
declare @searchitems int = (SELECT COUNT(*) FROM @splitresults)
I dropped a list of potential candidates into a temporary table
SQL
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..
SQL
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.
 
Share this answer
 
v4
Comments
Herman<T>.Instance 11-Jun-20 10:10am    
Hi CHill60,
I have accepted your solution, cause this works in a nice way.Except.... If I have n keywords and all keywords must exist not any but all. It is doing OR now in stead of AND.

My code:
DECLARE @searchtext NVARCHAR(1000)= 'vessel ship container';

;WITH containsfields as (SELECT [Value] from dbo.SplitString(@searchtext, ' '))
SELECT DISTINCT pv.[Id],
[ProjectNo],
[MainProjectNo],
[ProjectDescription],
[CustomerNo],
[CustomerName],
[Status],
CASE
WHEN [ProjectTypeCode] = 'NULL'
THEN NULL
ELSE [ProjectTypeCode]
END AS [ProjectTypeCode],
[ManagerName],
CASE
WHEN [EngineerName] = 'NULL'
THEN NULL
ELSE [EngineerName]
END AS [EngineerName],
FROM [dbo].[IProjectView] AS pv
INNER JOIN containsfields as cf
on CHARINDEX(cf.Value, pv.SearchLine) > 0
ORDER BY ProjectNo;


SearchLine is the concatenation of fields in the view.
How to change the query to have all keywords found in 1 line?
CHill60 12-Jun-20 5:23am    
In my "Approach 1" with multiple ON conditions you can change from OR to AND i.e.
SELECT A.*
FROM @project A
INNER JOIN @splitresults B ON CHARINDEX(B.dataitem, A.textitem1) > 0 AND CHARINDEX(B.dataitem, A.textitem2) > 0
Just repeat that pattern for all the columns you want to check
Herman<T>.Instance 12-Jun-20 10:35am    
I can have n splitresults but they all must occur within the pv.SearchLine
CHill60 12-Jun-20 11:03am    
Ah! I was looking at that the wrong way around. I'll have another think about it
CHill60 12-Jun-20 12:00pm    
I've updated my solution. It's not elegant but it worked on my dummy data. Can you give it a try?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900