Fam Wencong, Kenneth

My Diary

A Simple T-SQL Search Script

Published: Sunday, February 16, 2020

Below is a T-SQL search script. Modify it to suit your needs.

	DECLARE @temptable1 TABLE (
		[id] bigint,
		[count] int
	)

	DECLARE @temptable2 TABLE (
		[id] bigint,
		[name] nvarchar(1000)
	);

	INSERT INTO @temptable2 VALUES (1,'Tom'), (2,'Jerry'), (3,'Kate'), (4,'Diana');
	
	DECLARE @q nvarchar(MAX) = N'Tom';

	DECLARE @id bigint;  
	DECLARE @document nvarchar(MAX); 

	DECLARE Seach_Cursor CURSOR FOR  
	SELECT [id], [name]   
	FROM @temptable2 as u    
	OPEN Seach_Cursor;  
	FETCH NEXT FROM Seach_Cursor INTO @id, @document;   
	WHILE @@FETCH_STATUS = 0  
	   BEGIN  
			DECLARE @count int;
			IF(@q<>'')
				SET @count = (SELECT SUM(test.c) as col FROM (
				SELECT
					(len(@document) - len(replace(@document, value, ''))) / len(value) as c
				FROM 
					STRING_SPLIT(@q, ' ')
					) as test);
			ELSE
				SET @count = len(@document);
			INSERT INTO @temptable1 VALUES (@id,@count);
			FETCH NEXT FROM Seach_Cursor INTO @id, @document; 		
	   END;  
	CLOSE Seach_Cursor;  
	DEALLOCATE Seach_Cursor;  

	SELECT t.id as id,
	u.name as name,
	t.count as [count]
	FROM @temptable1 AS t INNER JOIN @temptable2 AS u ON t.id=u.id 
	ORDER BY [count] DESC;
Let me explain the script above. First, we create a table (@temptable1) to store our search results. The other table, @temptable2, is our main table. We then insert 4 rows. We then use cursors to loop through our records, inserting the number of times the search query appears in the dataset. Finally, we query for the results.