Prefix Searches: LIKE, LEFT and CHARINDEX

Prefix searches using LIKE, LEFT and CHARINDEX can return identical rows through different access paths. Compare both the matches and the actual plan. A shorter expression does not prove a faster search.

Use the same literal prefix and collation

The indexed temporary table contains 10,012 made-up names. Its varchar(50) column uses Latin1_General_100_CI_AS. Each query searches for the ordinary prefix pre. This case-insensitive collation lets PRElude match the lowercase literal.

Run this setup first in a new query window. It builds the table the queries below use.

DROP TABLE IF EXISTS #PrefixNames;

CREATE TABLE #PrefixNames (
    Id int NOT NULL PRIMARY KEY,
    Name varchar(50) COLLATE Latin1_General_100_CI_AS NULL
);

-- 10,000 filler rows
;WITH Digits(n) AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n))
INSERT #PrefixNames (Id, Name)
SELECT a.n + 10*b.n + 100*c.n + 1000*d.n,
       'Zulu' + CONVERT(varchar(10), a.n + 10*b.n + 100*c.n + 1000*d.n)
FROM Digits AS a CROSS JOIN Digits AS b CROSS JOIN Digits AS c CROSS JOIN Digits AS d;

-- the rows the examples use
INSERT #PrefixNames VALUES
(10001, 'pre'), (10002, 'prefix'), (10003, 'PRElude'), (10004, 'prefix'), (10005, 'present'),
(10006, 'pr'), (10007, ''), (10008, NULL),
(10009, 'a_b'), (10010, 'axb'), (10011, 'a%b'), (10012, 'a[b');

CREATE INDEX IX_PrefixNames_Name ON #PrefixNames (Name) INCLUDE (Id);

SELECT Id, Name FROM #PrefixNames
WHERE Name LIKE 'pre%' ORDER BY Id;

SELECT Id, Name FROM #PrefixNames
WHERE LEFT(Name, 3) = 'pre' ORDER BY Id;

SELECT Id, Name FROM #PrefixNames
WHERE CHARINDEX('pre', Name) = 1 ORDER BY Id;

The three queries returned exactly the same five identified rows on SQL Server 2025. Two different rows contain prefix. Both remain in every result. The NULL, empty and shorter pr values do not match.

Read the observed access paths

The LIKE plan used an index seek and a sort. The LEFT and CHARINDEX plans each used a clustered index scan. These are observed choices for this data, index, collation and compilation. They are not an elapsed-time benchmark or a promise that every prefix query will seek.

The prefix predicate can expose a range on the indexed Name column. Applying a function to that column can change available access choices. Still inspect the natural plan for your query. Selectivity, types, collation and indexes can change the outcome.

A wildcard-bearing prefix is a different comparison

An underscore matches one character in an unescaped pattern. In this example, a_b matches four different three-character strings. Literal underscores and percent signs need a pattern that expresses literal meaning.

SELECT Id, Name FROM #PrefixNames WHERE Name LIKE 'a_b';
SELECT Id, Name FROM #PrefixNames WHERE Name LIKE 'a[_]b';
SELECT Id, Name FROM #PrefixNames WHERE Name LIKE 'a!%b' ESCAPE '!';
SELECT Id, Name FROM #PrefixNames WHERE Name LIKE 'a[[]b';

The three escaped patterns each matched one intended row: a_b, a%b and a[b. These are exact-match pattern tests. Adding a final unescaped percent sign would make them prefix tests. User input also needs a deliberate escaping rule for the selected escape character.

Keep literal text separate from pattern instructions when comparing formulations. A CHARINDEX search treats its substring differently from a wildcard-bearing LIKE pattern. Check that both queries answer the same question first. Then compare their actual work under the intended data and collation.

DROP TABLE IF EXISTS #PrefixNames;

A shorter expression is not a faster search, it is a different access path.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.