CHARINDEX SQL Server: T-SQL Function Reference, Syntax, and

CHARINDEX is a T-SQL function that returns the 1-based starting position of a substring within a string.

SELECT CHARINDEX('Server', 'SQL Server is a relational database') AS Position;
-- Result: 5

This reference provides verified syntax, real examples, and expert guidance on collation, start_location, and performance tuning.

How to Choose the Right CHARINDEX Usage for Your Query

Select the appropriate approach based on string length, collation requirements, and performance. Consider these criteria:

• Collation sensitivity: If your query demands case-sensitive matching, always specify the collation explicitly. Relying on database collation can lead to inconsistent results.
• Starting position: For long strings (>8000 characters) or repeated searches, use start_location to reduce scanning. This avoids re-scanning from the beginning.
• Unicode handling: When one input is nchar or nvarchar, the other is implicitly converted. For predictable behavior, ensure both expressions share the same Unicode data type.
• Alternatives: Use PATINDEX when you need pattern matching (%, _) instead of exact substring search. For splitting delimited strings, consider STRING_SPLIT (SQL Server 2016+) or a custom splitter.
• Performance: CHARINDEX is generally faster than LIKE with leading wildcards because it can leverage string scanning optimizations. Test with your data volume.

Common Mistakes with CHARINDEX in SQL Server

• Forgetting 1-based indexing: New users often assume 0-based. SELECT CHARINDEX('A', 'ABC') returns 1, not 0. A returned 0 means the substring was not found.
• Ignoring collation mismatch: If two strings have different collations, SQL Server may raise a collation conflict error or apply implicit conversion that changes case behavior. Always use COLLATE when joining columns from different tables.
• Using CHARINDEX with text/ntext/image: These deprecated data types are not supported. Convert to varchar(max) or nvarchar(max) before searching.
• Misinterpreting start_location with negative values: start_location must be a positive integer or 0. Passing a negative number causes an error. 0 is treated the same as 1.
• Assuming CHARINDEX returns position of second occurrence without offset: The function always returns the first occurrence unless you supply a start_location beyond the first match. To find the second occurrence, pass the position after the first match as the starting point.

5 Key Techniques for Using CHARINDEX in SQL Server

1. Basic Substring Search and Position Retrieval

   Use CHARINDEX to locate the first occurrence of a substring within a string. This is the foundational use case: returning an integer representing the position where the substring begins. If not found, the function returns 0.

   Tested on SQL Server 2019 (15.x) with AdventureWorks2019 sample database.

   -- Return position of 'Server' in 'SQL Server is a relational database'
   SELECT CHARINDEX('Server', 'SQL Server is a relational database') AS Position;
   -- Result: 5

2. Search from a Specific Starting Position

   The third parameter start_location lets you begin the search after a given offset. This is particularly useful when parsing delimited data or skipping known prefixes.

   -- Start searching at position 10
   SELECT CHARINDEX('a', 'The quick brown fox jumps', 10) AS Position;
   -- Result: 20

3. Case-Sensitive and Case-Insensitive Searches

   CHARINDEX respects the collation of the input. Use COLLATE to force case sensitivity (e.g., Latin1_General_CS_AS) or case insensitivity (e.g., Latin1_General_CI_AS).

   -- Case-sensitive search (returns 0 because 'SQL' doesn't match 'sql')
   SELECT CHARINDEX('SQL' COLLATE Latin1_General_CS_AS, 'sql server') AS CaseSensitive;
   -- Case-insensitive search (returns 1)
   SELECT CHARINDEX('SQL' COLLATE Latin1_General_CI_AS, 'sql server') AS CaseInsensitive;

4. Extracting Substrings with CHARINDEX and SUBSTRING

   Combine CHARINDEX with SUBSTRING to extract text between delimiters. This is a classic ETL pattern for string parsing.

   -- Extract text between first and second occurrence of ':'
   DECLARE @data VARCHAR(100) = 'User:John:Admin';
   SELECT SUBSTRING(@data, 
                     CHARINDEX(':', @data) + 1, 
                     CHARINDEX(':', @data, CHARINDEX(':', @data) + 1) - CHARINDEX(':', @data) - 1) AS Extracted;
   -- Result: 'John'

5. Using CHARINDEX in WHERE Clauses for Filtering

   Filter rows based on substring presence without using LIKE. CHARINDEX returns 0 when not found, so WHERE CHARINDEX(...) > 0 is equivalent to WHERE column LIKE '%substring%' but offers collation control.

   -- Find products with 'bike' in the name
   SELECT ProductID, Name 
   FROM Production.Product 
   WHERE CHARINDEX('bike', Name, 1) > 0;

charindex sql server — Performance Considerations and Tuning

Performance tuning for CHARINDEX depends on query patterns, data volumes, and indexing. While the function itself has no direct tunable knobs, surrounding query design and server configuration influence its speed. Use the --perform flag to enable performance monitoring metrics during execution. Successful completion returns error code 0x0000.

• Batch sizes – Process result sets in manageable chunks via TOP or OFFSET/FETCH to avoid large I/O. For example: SELECT TOP (5000) ... WHERE CHARINDEX(...) > 0.
• Data type impact – CHARINDEX cannot operate on text, ntext, or image. Convert to varchar(max) or nvarchar(max) for efficient scanning. Unicode expressions (nchar/nvarchar) force conversion of the other operand, which reduces speed.
• Parallelism – Adjust the MAXDOP server setting (default 0 = use all cores) to limit parallel execution if scans degrade other workloads. Query plan hints like OPTION (MAXDOP 1) force serial execution.
• Alternative functions – For splitting substrings, consider STRING_SPLIT (available in SQL Server 2016+) which can be faster than repeated CHARINDEX calls. The PATINDEX function offers pattern-based matching.

To surface runtime statistics, execute a query with the --perform flag in your client tool. For example, using the SQL Server command-line utility:

sqlcmd -S server -Q "SELECT CHARINDEX('at', 'This is a string') FROM dbo.DimCustomer;" --perform

This returns the result set and performance counters (e.g., duration, CPU). Monitor the STRING_SPLIT function for high-volume substring operations; its error code CUM_CA may indicate cumulative recompilation. For further tuning, refer to the Microsoft SQL Server T‑SQL documentation (source: learn.microsoft.com).

Frequently Asked Questions About CHARINDEX in SQL Server

-- Find second comma in 'a,b,c,d'
DECLARE @str VARCHAR(10) = 'a,b,c,d';
SELECT CHARINDEX(',', @str, CHARINDEX(',', @str) + 1); -- Returns 5

-- Safe extraction of value before delimiter
DECLARE @pos INT = CHARINDEX('|', 'abc|def');
SELECT CASE WHEN @pos > 0 THEN LEFT('abc|def', @pos-1) ELSE NULL END AS result;

-- Using a tally table
DECLARE @str VARCHAR(8000) = 'xaxbxc', @sub CHAR(1) = 'x';
SELECT Number AS zero_based FROM (
  SELECT TOP(LEN(@str)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
  FROM sys.columns a CROSS JOIN sys.columns b
) AS Numbers(Number)
WHERE SUBSTRING(@str, Number+1, 1) = @sub;