I’d like some help to expand a query that I use:
Basically this query enables a space delimited string to be passed into it which is then used as a search parameter. It finds the words not matter which order they appear.
This has been working for a while. Now there is a requirement to search more than 1 column.
I have tried numerous ways but can’t get this to work correctly. The best result I have is to create two statements and union them together – which is less than ideal
Here is the code, any suggestions appreciated.
ALTER PROCEDURE [dbo].[Custom_Search_Items]
DECLARE @FilterTable TABLE (Data VARCHAR(512))
INSERT INTO @FilterTable (Data)
SELECT DISTINCT S.Data
FROM fnSplit(' ', @searchString ) S
T.item_number AS Number,
T.ver AS Version,
T.description AS Description,
INNER JOIN @FilterTable F1 ON T.description LIKE '%' + F1.Data + '%'
LEFT JOIN @FilterTable F2 ON T.description NOT LIKE '%' + F2.Data + '%'
F2.Data IS NULL
ALTER FUNCTION [dbo].[ fnSplit] ( @sep CHAR(1), @str VARCHAR(512) )
RETURNS TABLE AS
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @str)
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @str, stop + 1)
WHERE stop > 0
pn AS Id,
SUBSTRING(@str, start, CASE WHEN stop > 0 THEN stop - start ELSE 512 END) AS Data
Basically I need it to search the T.item_number field as well as the T.Description field