Last post Nov 20, 2014 11:11 AM by sen338
Nov 20, 2014 10:29 AM|0belix|LINK
I'm trying to find out how to create an SQL Procedure that receives a string containing space delimited values (unknown number of values), and then search for each of the values inside some columns of a table, finally returning the rows where there is at
least one match. I would also be great if the results came ordered by them most matches.
Keep in mind that i'm talking about a large database, so performance impact is a very important issue.
Can someone help or direct me, please?
Nov 20, 2014 11:11 AM|sen338|LINK
First Split the words and then search each word with table
Use the below split function
CREATE FUNCTION [dbo].[fnSplitString]
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
Got it from Here
then Make a join like below , performance may hit, you need to make your your table column has proper index .
Select * from T where C like (select '%' + splitdata + '%' from dbo.fnSplitString('Querying SQL Server',''));
Even you can search using full index like belowSELECT *
WHERE CONTAINS(C, '"David" OR "Robi" OR "Moses"');