LIKE Search: Literal Input, Wildcards, and Index Use

A user searching for a product code containing an underscore usually expects an underscore, not any character. Parameterization prevents input from becoming SQL syntax, but it does not remove LIKE wildcard semantics. Secure query construction and correct search behavior are separate responsibilities.

Decide whether input is text or a pattern

For a literal prefix search, escape the chosen escape character first, then percent, underscore, and opening bracket. Finally append the one wildcard that the application intentionally provides. The order matters: escaping the escape character last would also modify escape characters added during earlier replacements.

CREATE TABLE #Names(Name nvarchar(100) NOT NULL);
INSERT #Names VALUES(N'A_% report'),(N'ABC report'),(N'A! report');
DECLARE @input nvarchar(100)=N'A_%';
DECLARE @pattern nvarchar(201);
SET @pattern=REPLACE(@input,N'!',N'!!');
SET @pattern=REPLACE(@pattern,N'%',N'!%');
SET @pattern=REPLACE(@pattern,N'_',N'!_');
SET @pattern=REPLACE(@pattern,N'[',N'![')+N'%';
SELECT Name FROM #Names
WHERE Name LIKE @pattern ESCAPE N'!'
ORDER BY Name;

The example searches for names beginning with the literal A_%. It should match A_% report and not ABC report. The exclamation mark is the chosen escape character. A user's own exclamation mark is doubled before other transformations, preserving it as ordinary text.

The input variable is bounded, while the pattern variable has room for expansion. Escaping can double the character count, and the suffix adds another character. Size the actual client parameter accordingly; silent truncation can remove an escape or the final wildcard and change results. Match the parameter type to the searchable column instead of forcing an unnecessary column conversion.

Search semantics determine the access path

A prefix such as ABC% can often use an ordered index to narrow the search range. A leading wildcard such as %ABC% generally cannot provide the same starting range on a conventional index. Adding TOP limits returned rows but does not guarantee that few rows are examined, especially when matches are rare or a separate sort is required.

Case and accent behavior follows collation. Define whether the application considers cafe and café equivalent before selecting a collation or normalizing text. Wrapping every indexed value in UPPER or another function can complicate index use; a deliberate schema and comparison contract is easier to maintain than scattered transformations in queries.

Whitespace needs a policy too. Fixed-length char parameters may introduce padding, while trimming input can intentionally change what users search for. Decide whether leading and trailing spaces are meaningful for codes and names. Test the actual parameter types and database collation, not only a literal pasted into SSMS.

Keep the endpoint predictable

An empty prefix becomes a match-all pattern after the suffix is added. Treat that as an explicit application decision: reject empty searches, require a minimum length, or provide a bounded browse mode. Apply authorization filters before returning matches and use a stable ordering with a unique tie-breaker for pagination.

Full-text search is useful for linguistic word matching, but it is not a transparent replacement for arbitrary substring search over product codes. Tokenization, language rules, and punctuation affect the result. A suffix-search requirement may justify a deliberately maintained reverse-string index; that is a separate data-model choice with its own update cost.

Build a test set containing percent signs, underscores, brackets, the escape character, accented text, spaces, and no-match values. Compare expected row identities, not just result counts. Then test common and rare prefixes with realistic data volume, capturing logical reads and duration.

Keep the SQL statement parameterized even after wildcard escaping. Escaping LIKE metacharacters solves pattern interpretation, not injection. A reliable search endpoint has both properties: input remains data, and the matching rules correspond to what the user was told the search would do.

Technical references: Microsoft Learn: LIKE · Microsoft Learn: Collation.