Select LIKE '%string%', without FULL-TEXT-SEARCH in SQL Server

Question

-- create table
CREATE TABLE dbo.Tests
(
    Id BIGINT NOT NULL IDENTITY,
    String NVARCHAR(100),
    StringReversed AS REVERSE(String),
    CONSTRAINT PK_Tests PRIMARY KEY (Id), 
)
CREATE NONCLUSTERED INDEX IX1 ON dbo.Tests(String)
CREATE NONCLUSTERED INDEX IX2 ON dbo.Tests(StringReversed)


-- populate table with 100k random strings (for testing)
DECLARE @I INT = 100000
WHILE @I > 0
BEGIN
    INSERT INTO Tests(String)
    SELECT CONVERT(varchar(36), NEWID())
    SET @I = @I - 1
END

-- how do i do a LIKE '%STRING%' search which uses the index?
SELECT String, StringReversed FROM Tests WHERE String LIKE '%0A7EB%'

SELECT String, StringReversed FROM Tests 
    WHERE String LIKE '0A7EB%' OR StringReversed LIKE 'BE7A0%'

Can you help me with this? I am trying to implement a full-text-search alternative to be able to do a LIKE '%STRING%' WHERE clause.

I'm stuck not quite sure if this is even possible to implement? Let's just assume that FULL TEXT SEARCH is not possible, and I need to use index.

This is a prod issue and we need to do a LIKE '%search%' in the string column. I just read here: SQL Server: Index columns used in like? that we can do a reverse?

Hope you can help me, thanks a lot.

Simply put, you can't. When you have leading wildcards you have created a nonSARGable predicate. Think about it....you have to look at every single row to know if it contains the string you are looking for. — Sean Lange, Jan 12 '18 at 14:45
Can you try out `where CONTAINS(String, '0A7EB')` just out of curiosity ` — Veljko89, Jan 12 '18 at 14:49
Nope, your current query doesn't work because both clauses assume that the substring must appear at either the beginning or end of the string (and they're 100% redundant anyway). Check out [this](https://sqlperformance.com/2017/02/sql-indexes/seek-leading-wildcard-sql-server), [this](https://sqlperformance.com/2017/02/sql-performance/follow-up-1-leading-wildcard-seeks), and [this](https://sqlperformance.com/2017/09/sql-performance/sql-server-trigram-wildcard-search) for potential trigram-style solutions. — Aaron Bertrand, Jan 12 '18 at 14:54
I fail to see the question what you think is wrong?. But as @SeanLange said `LIKE '%search%'` wont use index but that doesnt stop you to use it. If you need improve the performance you switch to `Full Text Search` — Juan Carlos Oropeza, Jan 12 '18 at 15:05
Thanks for the responses, I'll check the 3 links you gave. Well, the question is how do I implement leading wildcards while i can still use indexes, without using full text search... Sad thing seems the replies mean i still need to put more work to make it happen... — JL Dajoyag, Jan 12 '18 at 15:40

score 0 · Answer 1 · answered Jan 15 '18 at 13:44

Update: I tried the string fragments approach.

-- create table
CREATE TABLE dbo.Tests
(
    Id BIGINT NOT NULL IDENTITY,
    String NVARCHAR(100),
    CONSTRAINT PK_Tests PRIMARY KEY (Id), 
)
GO

-- create table for Test String Fragments
CREATE TABLE dbo.TestStringFragments(
    Id BIGINT NOT NULL IDENTITY,
    TestId BIGINT NOT NULL,
    Fragment NVARCHAR(100),
    CONSTRAINT PK_TestStringFragments PRIMARY KEY (Id)
)
CREATE NONCLUSTERED INDEX IX_TestStringFragments_Fragment ON dbo.TestStringFragments(Fragment)
CREATE NONCLUSTERED INDEX IX_TestStringFragments_TestId ON dbo.TestStringFragments(TestId)
GO

-- create UDF to generate string fragments
CREATE FUNCTION dbo.CreateStringFragments(@input nvarchar(100))
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN 
(
    WITH x(x) AS 
    (SELECT 1 UNION ALL SELECT x+1 FROM x WHERE x < (LEN(@input)))
    SELECT Fragment = SUBSTRING(@input, x, LEN(@input)) FROM x
)
GO

-- create trigger for the Tests table
CREATE TRIGGER dbo.Tests_MaintainStringFragments
ON dbo.Tests
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON

    DELETE TSF FROM dbo.TestStringFragments AS TSF
    INNER JOIN deleted ON TSF.TestId = deleted.Id

    INSERT dbo.TestStringFragments(TestId, Fragment)
    SELECT inserted.Id, fragments.Fragment
    FROM inserted 
    CROSS APPLY dbo.CreateStringFragments(inserted.String) AS fragments

END
GO

-- populate table with 100k random strings (for testing)
DECLARE @I INT = 100000
WHILE @I > 0
BEGIN
    INSERT INTO Tests(String)
    SELECT CONVERT(varchar(36), NEWID())
    SET @I = @I - 1
END

Able to replicate the LIKE '%string%' code via the fragments table.

SELECT T.* FROM Tests T WITH(NOLOCK)
WHERE T.String LIKE '%CBB2%'

SELECT T.* FROM Tests T WITH(NOLOCK)
INNER JOIN TestStringFragments TSF WITH(NOLOCK) ON T.Id = TSF.TestId
where TSF.Fragment LIKE 'CBB2%'

My new execution plan is 61% for the first query, 39% for the second one. I'll check the trigrams approach.

Select LIKE '%string%', without FULL-TEXT-SEARCH in SQL Server

1 Answers1