I have a procedure that selects an offset of rows from a table:
SELECT * --table contains ID and Name columns FROM Names ORDER BY ID OFFSET @Start ROWS FETCH NEXT @Length ROWS ONLY
In addition to @Start and @Length parameters, the procedure also receives @SearchValue NVARCHAR(255) parameter. @SearchValue contains a string of values delimited by a space, for example '1 ik mi' or 'Li 3'.
What I need is to query every record containing all of those values. So, if the @SearchValue is '1 ik mi', it should return any records that contain all three values: ‘1’, ‘mi’, and ‘ik’. Another way to understand this is by going here, searching the table (try searching 00 eer 7), and observing the filtered results.
I have the freedom to change the delimiter or run some function (in C#, in my case) that could format an array of those words.
Below are our FAILED attempts (we didn’t try implementing it with OFFSET yet):
Select ID, Name From Names Where Cast(ID as nvarchar(255)) in (Select value from string_split(@SearchValue, ' ')) AND Name in (Select value from string_split(@SearchValue, ' '))
SELECT ID, Name FROM Names WHERE @SearchValueLIKE '% ' + CAST(ID AS nvarchar(20)) + ' %' AND @SearchValueLIKE '% ' + Name + ' %';
We used Microsoft docs on string_split for the ideas above.
Tomorrow, I will try to implement this solution, but I’m wondering if there’s another way to do this in case that one doesn’t work. Thank you!
Advertisement
Answer
Your best bet will be to use a FULL TEXT index. This is what they’re built for.
Having said that you can work around it.. BUT! You’re going to be building a query to do it. You can either build the query in C# and fire it at the database, or build it in the database. However, you’re never going to be able to optimise the query very well because users being users could fire all sorts of garbage into your search that you’ll need to watch out for, which is obviously a topic for another discussion.
The solution below makes use of sp_executesql, so you’re going to have to watch out for SQL injection (before someone else picks apart this whole answer just to point out SQL injection):
DROP TABLE #Cities;
CREATE TABLE #Cities(id INTEGER IDENTITY PRIMARY KEY, [Name] VARCHAR(100));
INSERT INTO #Cities ([Name]) VALUES
('Cooktown'),
('South Suzanne'),
('Newcastle'),
('Leeds'),
('Podunk'),
('Udaipur'),
('Delhi'),
('Murmansk');
DECLARE @SearchValue VARCHAR(20) = 'ur an rm';
DECLARE @query NVARCHAR(1000);
SELECT @query = COALESCE(@query + '%'' AND [Name] LIKE ''%', '') + value
FROM (Select value from string_split(@SearchValue, ' ')) a;
SELECT @query = 'SELECT * FROM #Cities WHERE [Name] LIKE ''%' + @query + '%''';
EXEC sp_executesql @query;