CHARINDEX() and POSITION() in SQL

Introduction

CHARINDEX and POSITION are string-search functions that return the starting position of one text value inside another. They solve the same general problem but use different syntax and are associated with different database systems. SQL Server commonly uses CHARINDEX, while PostgreSQL and several other systems support the standard-style POSITION expression.

-- SQL Server
SELECT CHARINDEX('SQL', 'Learn SQL Today') AS match_position;

-- PostgreSQL-style
SELECT POSITION('SQL' IN 'Learn SQL Today') AS match_position;

-- Result in both examples: 7

Locating text is useful for validation, delimiter discovery, data profiling, conditional logic, and extraction with LEFT, RIGHT, or SUBSTRING. The position is usually one-based, so the first character has position one. A result of zero commonly means that the substring was not found, while NULL input normally produces NULL.

The syntax is short, but reliable use requires more than finding a number. Queries must handle missing delimiters, repeated matches, case and collation rules, Unicode behavior, index limitations, and database portability. Repeated parsing can also reveal that structured attributes belong in separate columns. This tutorial connects basic syntax to those production decisions.

CHARINDEX Syntax in SQL Server

SQL Server places the text to find first and the text to search second. An optional third argument specifies where the search should begin.

CHARINDEX(substring, string_expression [, start_location])
SELECT CHARINDEX('Java', 'Learn Java Programming') AS match_position;

-- Result: 7

The returned value identifies where the complete substring begins. CHARINDEX searches for literal text rather than a wildcard pattern.

POSITION Syntax

POSITION expresses the relationship with the IN keyword. The searched substring appears before IN, and the containing string appears after it.

POSITION(substring IN string_expression)
SELECT POSITION('Java' IN 'Learn Java Programming') AS match_position;

-- Result: 7

Do not write POSITION as if it were CHARINDEX. The argument order and punctuation differ even though the conceptual result is the same.

Positions Start at One

SQL text positions are normally one-based. In DATABASE, D is position 1, A is position 2, and the substring BASE begins at position 5.

SELECT CHARINDEX('BASE', 'DATABASE') AS sql_server_position;

SELECT POSITION('BASE' IN 'DATABASE') AS standard_position;

-- Result: 5

Application languages often use zero-based indexes, so integration code must not copy the SQL result directly into a zero-based API without conversion.

Searching for a Single Character

The searched value may contain one character. Delimiters such as hyphens, slashes, spaces, at signs, and periods are common targets.

SELECT CHARINDEX('-', 'ELEC-1001') AS hyphen_position;

-- Result: 5

The result says where the delimiter occurs; it does not validate the rest of the code. A malformed value can still contain a hyphen at position five.

Searching for Multiple Characters

Both functions can search for a complete word or sequence. The result is the starting position of the whole match.

SELECT CHARINDEX('Server', 'SQL Server Database') AS match_position;
SELECT POSITION('Server' IN 'SQL Server Database') AS match_position;

-- Result: 5

If the substring overlaps another possible match, the database still returns the first occurrence according to its documented matching behavior.

Using the Functions with Columns

A position can be calculated for every row. The following queries identify the location of the at sign in each email value.

-- SQL Server
SELECT email,
       CHARINDEX('@', email) AS at_position
FROM app_user;

-- PostgreSQL
SELECT email,
       POSITION('@' IN email) AS at_position
FROM app_user;
emailat_position
john@example.com5
mary@test.com5
david@company.org6

Rows with zero, NULL, or more than one at sign require separate validation. Position discovery is one step, not proof that the address is deliverable.

What Happens When Text Is Not Found

Common CHARINDEX and POSITION usage returns zero when a non-NULL substring does not occur.

SELECT CHARINDEX('Java', 'Learn SQL') AS match_position;
SELECT POSITION('Java' IN 'Learn SQL') AS match_position;

-- Result: 0

This result is useful in predicates, but it becomes dangerous when immediately reduced by one to form an extraction length. A missing delimiter can turn zero into a negative value and cause an error.

Checking Whether Text Exists

A position greater than zero means that a match was found.

-- SQL Server
SELECT course_name
FROM courses
WHERE CHARINDEX('SQL', course_name) > 0;

-- PostgreSQL
SELECT course_name
FROM courses
WHERE POSITION('SQL' IN course_name) > 0;

This form is appropriate when the position may also be selected or reused. If only containment matters, LIKE or another search feature may communicate the intention more directly.

Extracting Text Before a Delimiter

Position functions are frequently combined with LEFT. For ELEC-1001, the hyphen begins at position five, so the four preceding characters form the category.

SELECT LEFT(
           product_code,
           CHARINDEX('-', product_code) - 1
       ) AS category_code
FROM products;

The expression assumes that a hyphen exists. Production parsing should test that assumption before calculating a length.

Extracting Text After a Delimiter

SUBSTRING can begin one character after the delimiter and continue to the end. The exact length expression varies by DBMS.

-- SQL Server
SELECT SUBSTRING(
           product_code,
           CHARINDEX('-', product_code) + 1,
           LEN(product_code)
       ) AS product_number
FROM products;

Passing the full string length as the requested substring length is a common SQL Server shortcut because SUBSTRING returns only the available remaining characters.

Safe Delimiter Parsing

A CASE expression can guard extraction when the delimiter is missing. This avoids a negative length and makes invalid input visible.

SELECT email,
       CASE
           WHEN CHARINDEX('@', email) > 0
           THEN LEFT(email, CHARINDEX('@', email) - 1)
           ELSE NULL
       END AS local_part
FROM app_user;

For strict validation, return an error category or route the row to an exception table rather than silently replacing malformed data with NULL. The appropriate response depends on whether the query is a report, import, migration, or transaction.

Parsing an Email Address

The at sign can divide a simple email string into a local part and domain for profiling or routing. SQL Server can calculate the position once with CROSS APPLY to avoid repeating the expression.

SELECT u.email,
       LEFT(u.email, p.at_position - 1) AS local_part,
       SUBSTRING(u.email, p.at_position + 1, LEN(u.email)) AS domain_part
FROM app_user AS u
CROSS APPLY (VALUES (CHARINDEX('@', u.email))) AS p(at_position)
WHERE p.at_position > 1;

This is not complete email validation. Addresses can contain complex syntax, internationalized domains, and policy rules. Use an application or dedicated validation process for user-facing identity workflows.

Parsing a Simple Name

The first space can separate two display fragments in controlled sample data.

SELECT LEFT(full_name, CHARINDEX(' ', full_name) - 1) AS first_part,
       SUBSTRING(full_name, CHARINDEX(' ', full_name) + 1, LEN(full_name))
           AS remaining_part
FROM employees
WHERE CHARINDEX(' ', full_name) > 0;

Do not model real personal names as exactly two space-separated tokens. Names can contain multiple words, prefixes, suffixes, punctuation, or no spaces. Preserve the user's preferred full name and store separate attributes only when the product genuinely needs them.

Searching from a Starting Position

SQL Server CHARINDEX accepts an optional start location. This is useful when the first part of a string should be skipped or when searching for another occurrence.

SELECT CHARINDEX('SQL', 'SQL SQL SQL', 5) AS match_position;

-- Result: 5

The start location controls where searching begins; the returned position still refers to the original string rather than a sliced string.

Finding the Second Occurrence

Find the first occurrence, add one, and begin a second search at that later position.

DECLARE @text VARCHAR(50) = 'SQL SQL SQL';
DECLARE @first_position INT = CHARINDEX('SQL', @text);

SELECT CHARINDEX('SQL', @text, @first_position + 1)
       AS second_position;

-- Result: 5

If the first search returns zero, the second starting position must be handled explicitly. For non-overlapping matches, some requirements begin after the full first substring rather than after one character.

Overlapping and Non-Overlapping Matches

The next starting position determines whether overlapping matches are considered. Suppose the source is AAAA and the searched substring is AA. Matches can begin at positions 1, 2, and 3 when overlaps are allowed. If the business rule counts non-overlapping occurrences, the next search should begin after the complete previous match, producing positions 1 and 3.

DECLARE @source VARCHAR(20) = 'AAAA';
DECLARE @find VARCHAR(20) = 'AA';
DECLARE @first INT = CHARINDEX(@find, @source);

SELECT @first AS first_position,
       CHARINDEX(@find, @source, @first + 1) AS overlapping_next,
       CHARINDEX(@find, @source, @first + LEN(@find)) AS non_overlapping_next;

Neither interpretation is universally correct. Searching a biological sequence, detecting repeated delimiters, and replacing tokens may require different rules. State the occurrence policy in tests so that a later optimization does not change meaning.

Position Arithmetic and Off-by-One Errors

Most parsing defects occur in the arithmetic around a correct position. To extract text before a delimiter, the length is usually position - 1. To begin after a one-character delimiter, the start is usually position + 1. A multi-character delimiter requires adding its full length.

DECLARE @value VARCHAR(100) = 'key::value';
DECLARE @delimiter VARCHAR(10) = '::';
DECLARE @position INT = CHARINDEX(@delimiter, @value);

SELECT LEFT(@value, @position - 1) AS key_part,
       SUBSTRING(@value, @position + LEN(@delimiter), LEN(@value))
           AS value_part
WHERE @position > 0;

Hard-coding + 1 would leave one colon in the result. Boundary tests should cover a delimiter at the first position, at the last position, repeated consecutively, and missing entirely. Naming the position and delimiter length makes the intent easier to review.

Finding Multiple Delimiters

A compound value such as USA-IT-1001 contains two hyphens. SQL Server can locate the second one by starting after the first.

SELECT CHARINDEX(
           '-',
           'USA-IT-1001',
           CHARINDEX('-', 'USA-IT-1001') + 1
       ) AS second_hyphen;

-- Result: 7

As expressions become nested, readability and error handling decline. Repeatedly parsing complex compound data usually indicates that separate columns or a proper parser would be more maintainable.

Finding the Last Occurrence

SQL Server does not give CHARINDEX a direct "search backward" option. A common technique reverses the text, finds the first delimiter in the reversed value, and translates that position back to the original.

SELECT LEN(filename)
       - CHARINDEX('.', REVERSE(filename))
       + 1 AS last_period_position
FROM files
WHERE CHARINDEX('.', filename) > 0;

Test filenames with multiple periods, no extension, leading periods, trailing periods, and multi-byte characters. Application parsing or a stored media type is often clearer than elaborate filename logic.

Case Sensitivity and Collation

Whether SQL matches sql depends on database configuration, collation, locale, and data type. SQL Server commonly derives CHARINDEX comparison behavior from the input collation. PostgreSQL comparisons are generally case-sensitive unless the expression or type applies different semantics.

-- Normalization pattern when lowercase matching is intentional
SELECT CHARINDEX('sql', LOWER(course_name)) AS match_position
FROM courses;

Normalizing in every query can affect indexes and international behavior. Prefer a clearly defined collation, normalized search column, or specialized search feature when case-insensitive searching is a core requirement.

Searching with Configurable Delimiters

Import systems often receive a delimiter as configuration rather than source code. Parameterization avoids concatenating values into dynamic SQL and permits multi-character delimiters, but the parser must define what an empty delimiter means and how consecutive delimiters are handled.

-- SQL Server stored-procedure fragment
DECLARE @delimiter VARCHAR(10) = '|';

SELECT source_value,
       CHARINDEX(@delimiter, source_value) AS delimiter_position
FROM import_staging
WHERE @delimiter <> '';

Delimiter configuration is not a substitute for a formal file parser. CSV, quoted values, escaped delimiters, embedded line breaks, and nested formats have grammar rules that CHARINDEX alone cannot interpret safely. Use a parser designed for the format, then load validated fields into typed staging columns.

NULL and Empty Inputs

If either the searched substring or containing text is NULL, these functions normally return NULL because no definite position can be calculated.

SELECT CHARINDEX('SQL', NULL) AS null_container,
       CHARINDEX(NULL, 'SQL') AS null_search;

Empty-string behavior should be verified for the target database. Oracle treats an empty string as NULL in ordinary SQL character semantics, while SQL Server and PostgreSQL distinguish empty text from NULL. Do not use COALESCE unless the business rule intentionally treats missing and empty values alike.

CHARINDEX Versus LIKE

CHARINDEX provides a numeric location, while LIKE evaluates a pattern in a predicate. Both can express containment.

WHERE CHARINDEX('SQL', course_name) > 0

-- Similar containment intention
WHERE course_name LIKE '%SQL%'

LIKE supports wildcard semantics, so percent and underscore have special meaning unless escaped. CHARINDEX searches for literal text. Choose based on required semantics, readability, collation, and measured performance.

CHARINDEX Versus PATINDEX

SQL Server PATINDEX locates a wildcard pattern rather than a literal substring. It is useful when the requirement concerns a class of characters.

SELECT PATINDEX('%[0-9]%', 'ABC123') AS first_digit_position;

-- Common result: 4

PATINDEX patterns follow SQL Server pattern and collation rules. For complex validation, a dedicated parser, regular-expression feature, or application validation may be easier to test and maintain.

POSITION Versus STRPOS

PostgreSQL also provides STRPOS. Its argument order resembles "container, substring" rather than POSITION's IN syntax.

SELECT POSITION('SQL' IN 'Learn SQL') AS position_result,
       STRPOS('Learn SQL', 'SQL') AS strpos_result;

-- Both return 7

Teams should standardize on one form within a codebase unless a specific reason favors the other. Consistency reduces argument-order mistakes.

MySQL Alternatives

MySQL supports POSITION, LOCATE, and INSTR for substring location. The functions express similar logic with different argument order.

SELECT POSITION('SQL' IN 'Learn SQL Today');
SELECT LOCATE('SQL', 'Learn SQL Today');
SELECT INSTR('Learn SQL Today', 'SQL');

-- Each returns 7

LOCATE can also accept a starting position. Choose one syntax consistently and confirm behavior for collations, multi-byte characters, and empty strings.

Oracle INSTR

Oracle commonly uses INSTR and offers optional start-position and occurrence arguments, making repeated-match searches concise.

SELECT INSTR('SQL SQL SQL', 'SQL', 1, 2) AS second_position
FROM dual;

-- Result: 5

Oracle's richer occurrence syntax does not make a query portable to CHARINDEX or POSITION. Isolate vendor-specific SQL when applications support several database engines.

Cross-Database Comparison

DatabaseCommon functionTypical form
SQL ServerCHARINDEXCHARINDEX(find, source, start)
PostgreSQLPOSITION / STRPOSPOSITION(find IN source)
MySQLPOSITION / LOCATE / INSTRLOCATE(find, source, start)
OracleINSTRINSTR(source, find, start, occurrence)

Function names, argument order, optional parameters, and edge-case behavior differ. A portable data-access layer should test every supported engine rather than translating names mechanically.

Unicode and Character Semantics

Text position does not always equal byte position. Unicode encodings can use several bytes for one character, and a user-perceived symbol can contain multiple code points. Each DBMS defines how its function counts characters for specific data types and collations.

Test accented text, combining marks, non-Latin scripts, emoji, and supplementary characters on the actual database version. If a returned position is passed to an application library, confirm that both systems use compatible indexing units. A SQL character position may not map directly to a UTF-16 code-unit index.

Filtering Performance

A predicate such as CHARINDEX('SQL', description) > 0 may require the database to inspect many row values. A leading-wildcard LIKE predicate has a similar limitation for an ordinary B-tree index. The cost grows with table size, text length, selectivity, and concurrent workload.

If the requirement is a prefix, course_name LIKE 'SQL%' can often use an index more effectively than CHARINDEX('SQL', course_name) = 1. Confirm with execution plans and realistic statistics because collation, parameterization, and index design affect the outcome.

Reading the Execution Plan

Performance review should distinguish the cost of locating text from the cost of reading rows. An execution plan may show a table or index scan because the database cannot seek directly to a substring in the middle of each value. The scan may be acceptable for a small reference table but expensive for millions of long descriptions.

Measure logical reads, elapsed time, CPU, memory grants, parallelism, and returned-row count with representative parameters. A computed or generated boolean or extracted component can be indexed when the search rule is stable. Creating an index for every possible substring is not practical; broad user-entered searching generally needs a full-text or specialized search index.

Parameter values matter. A rare literal and a common literal can have very different selectivity while sharing the same plan. Performance tests should include typical, absent, and highly frequent terms rather than one convenient example.

When to Use Full-Text Search

CHARINDEX and POSITION perform literal substring discovery. They do not provide linguistic tokenization, stemming, relevance ranking, stop-word handling, phrase indexes, or advanced query syntax. Scanning large article bodies with them is rarely a complete search architecture.

Use the database's full-text capabilities or a specialized search engine when users search documents, descriptions, logs, or knowledge-base content at scale. Keep position functions for small fields, deterministic parsing, validation, and cases where the exact character offset is genuinely needed.

Structured Data Should Use Columns

If every query splits USA-IT-1001 into country, department, and employee number, those components are probably independent attributes. Separate typed columns support constraints, foreign keys, indexes, joins, and statistics without repeated parsing.

SELECT country_code,
       department_code,
       employee_number,
       CONCAT(country_code, '-', department_code, '-', employee_number)
           AS display_code
FROM employees;

Preserve an externally assigned compound identifier when integrations require its exact form, but do not make every internal query rediscover known structure.

URL and Path Parsing Boundaries

Position functions can split a controlled internal path, such as separating a known host from a known route. They are not complete URL parsers. A real URL can contain a scheme, user information, port, encoded characters, query string, fragment, IPv6 literal, and slashes whose meaning depends on context.

-- Appropriate only for a controlled host/path format
SELECT source_url,
       POSITION('/' IN source_url) AS first_slash
FROM imported_links;

For externally supplied URLs, parse and validate with a standards-aware library in the application or ingestion layer. Do not use a simple substring test to allow redirects or confirm that a host belongs to a trusted domain. A value can contain trusted-looking text while identifying an unrelated host.

Log and Message Parsing

Operational logs often contain delimiters, but their format changes as services evolve. CHARINDEX or POSITION can support a one-time diagnostic query when the relevant marker is simple and stable. Building permanent analytics on ad hoc text parsing is more fragile.

SELECT log_line,
       CHARINDEX('request_id=', log_line) AS request_id_marker
FROM application_log
WHERE CHARINDEX('request_id=', log_line) > 0;

If request ID, severity, service, duration, and status are important dimensions, emit structured logs and store those values as fields. Structured logging improves indexing, type validation, retention, and correlation. Position functions remain useful for legacy cleanup during migration.

Security and Privacy Considerations

Finding a token inside text does not make it safe to display or store. Queries that locate authorization headers, account identifiers, email components, or secrets can expose sensitive values in result sets, logs, temporary tables, and monitoring tools. Limit selected columns, mask outputs where appropriate, and apply least-privilege access.

Substring checks are weak security validators. Confirming that a URL, filename, command, or header contains an allowed word does not prove that the complete value is safe. Security decisions require canonical parsing, allowlists, typed validation, and context-specific libraries.

Data Validation and Quality Checks

Position functions can identify obvious format problems. The following SQL Server query flags email values with no at sign or with one at either boundary.

SELECT email
FROM app_user
WHERE email IS NULL
   OR CHARINDEX('@', email) <= 1
   OR CHARINDEX('@', email) = LEN(email);

This remains a screening rule, not complete validation. Data-quality reports should categorize failures, count them by source and ingestion date, and preserve samples for investigation. Constraints or trusted ingestion code should prevent known invalid states where possible.

Monitoring Format Drift

A parser can remain syntactically correct while upstream data changes. Monitor the percentage of rows with missing delimiters, multiple delimiters, shifted positions, unexpected prefixes, and unusually long components. Break metrics down by producer, file, API version, and ingestion date so a change can be traced to its source.

SELECT import_source,
       COUNT(*) AS total_rows,
       SUM(CASE
               WHEN POSITION('|' IN source_value) = 0 THEN 1
               ELSE 0
           END) AS missing_delimiter_rows
FROM import_staging
GROUP BY import_source;

Alert on rates rather than only absolute counts because volume can vary. Keep representative invalid samples under suitable privacy controls. Monitoring complements validation: constraints stop known invalid records, while drift metrics reveal new assumptions that current rules do not express.

Reusing a Calculated Position

Complex queries are easier to read when a position is calculated once and named. SQL Server can use APPLY, while portable designs can use a common table expression.

WITH located_codes AS (
    SELECT product_code,
           POSITION('-' IN product_code) AS delimiter_position
    FROM products
)
SELECT product_code,
       LEFT(product_code, delimiter_position - 1) AS category_code
FROM located_codes
WHERE delimiter_position > 1;

Whether the optimizer physically evaluates the expression once is implementation-dependent. The main benefit is a single, testable definition and reduced risk that repeated expressions drift apart.

Migrating Parsed Data Safely

When delimiter parsing has become critical, introduce component columns through a controlled migration. First profile missing, repeated, and misplaced delimiters. Preview proposed values, route exceptions for review, and compare reconstructed strings with originals before updating any authoritative data.

Backfill in restartable batches, monitor locks and transaction logs, add constraints after exceptions are resolved, and update readers before writers. Keep the original external value when audit or integration requirements need it. The migration should include reports, exports, APIs, caches, and downstream warehouses rather than changing only the source table.

Testing Strategy

Tests should cover a match at the first character, middle, and end; no match; repeated and overlapping matches; empty text; NULL; whitespace; mixed case; accented and non-Latin text; emoji; long strings; and delimiters at invalid boundaries. Include values with one, several, and no separators.

Run the suite on every supported DBMS, version, character set, and collation. Verify result positions, extraction output, error handling, and integration with application indexing. Performance tests should compare functional predicates, LIKE patterns, normalized columns, and full-text options using realistic data distribution.

Build expected results independently rather than copying the same position formula into test setup. For each case, assert both the numeric location and any text extracted from it. Cross-check positions returned to application code, especially when the application indexes UTF-16 code units while the database counts characters differently. Migration tests should also prove that malformed rows are reported without stopping valid rows from being processed.

Common Mistakes

Frequent errors include expecting zero-based positions, reversing CHARINDEX arguments, writing POSITION without the IN keyword, and ignoring the zero returned for a missing delimiter. Developers also assume the first occurrence is the last, parse real names as two tokens, or treat the presence of an at sign as complete email validation.

At scale, using substring scans for document search can become expensive. At the schema level, deeply nested parsing often indicates that compound values should be decomposed into typed columns.

Best-Practice Checklist

  1. Remember that returned positions are normally one-based.
  2. Handle zero and NULL before using a position in extraction.
  3. Use the argument order and syntax required by the target DBMS.
  4. Calculate repeated positions once for readable queries.
  5. Distinguish literal substring search from wildcard patterns.
  6. Define case, accent, locale, and collation requirements.
  7. Test repeated, overlapping, and final occurrences explicitly.
  8. Use prefix predicates when the requirement is starts-with matching.
  9. Review execution plans for large-table containment searches.
  10. Use full-text search for document-scale linguistic retrieval.
  11. Store structured components in separate columns when practical.
  12. Test Unicode positions across database and application boundaries.

Interview Questions and Answers

What does CHARINDEX do? It returns the starting position of a literal substring in SQL Server.

What does POSITION do? It returns the starting position of a substring using the POSITION(find IN source) form.

What is returned when text is not found? Common implementations return zero for non-NULL input.

Are positions zero-based? No. SQL string positions are normally one-based.

Does CHARINDEX return every match? No. It returns the first match at or after its optional start location.

How can SQL Server find a second occurrence? Start another CHARINDEX search after the first match.

What is CHARINDEX versus LIKE? CHARINDEX returns a position for literal text; LIKE evaluates a wildcard pattern in a predicate.

What is CHARINDEX versus SUBSTRING? CHARINDEX locates text; SUBSTRING extracts text. They are often combined.

What does PostgreSQL offer besides POSITION? PostgreSQL also provides STRPOS.

What do MySQL and Oracle commonly use? MySQL supports POSITION, LOCATE, and INSTR; Oracle commonly uses INSTR.

Key Takeaway

CHARINDEX and POSITION answer a precise question: where does this substring begin inside that string? The returned location becomes useful when it is validated and combined with extraction, classification, or quality checks.

RELIABLE SUBSTRING LOCATION = CORRECT DBMS SYNTAX
                            + ONE-BASED POSITION AWARENESS
                            + ZERO AND NULL HANDLING
                            + COLLATION AND UNICODE TESTING
                            + INDEX-AWARE SEARCH DESIGN
                            + APPROPRIATE DATA MODEL

Use these functions when the actual position matters. Guard missing delimiters, handle repeated occurrences deliberately, and avoid turning literal substring scans into a substitute for structured columns or full-text search.