Search For Character In String Sql

8 min read

Searching for a specific character or sequence of characters within a string is one of the most fundamental operations in database management. In real terms, whether you are cleaning messy data, validating user input, or building complex search features, understanding how to search for character in string SQL scenarios is essential for any developer or data analyst. While the core concept remains consistent across platforms, the syntax and available functions vary significantly between database engines like MySQL, PostgreSQL, SQL Server, and Oracle That's the part that actually makes a difference..

Not the most exciting part, but easily the most useful Not complicated — just consistent..

Understanding the Basics of String Searching

At its core, searching a string involves scanning a sequence of characters to find a match for a specific pattern. This pattern can be a single character, a word, or a complex sequence defined by wildcards or regular expressions. The performance and syntax of these operations depend heavily on the underlying database engine and whether the search is case-sensitive.

Quick note before moving on.

Most relational databases offer three primary approaches:

  1. Position-Based Functions: Finding the exact index of a substring (CHARINDEX, INSTR, POSITION, LOCATE). This leads to Simple Pattern Matching: Using LIKE with wildcards (%, _). 2. But 3. Advanced Pattern Matching: Using Regular Expressions (REGEXP, SIMILAR TO, REGEXP_LIKE).

Choosing the right method impacts not only the correctness of your query but also its execution speed, especially on large datasets.

The Universal Standard: The LIKE Operator

The LIKE operator is the ANSI SQL standard for pattern matching and is supported by virtually every relational database system. It is the go-to tool for simple "contains" checks.

Wildcards in LIKE

  • % (Percent Sign): Represents zero, one, or multiple characters.
  • _ (Underscore): Represents exactly one single character.

Practical Examples

To find all customers whose last_name contains the character sequence 'son':

SELECT first_name, last_name
FROM customers
WHERE last_name LIKE '%son%';

This returns names like "Johnson", "Anderson", and "Sonny".

To find a specific character at a specific position—for example, finding phone numbers where the 4th character is a hyphen -:

SELECT phone_number
FROM contacts
WHERE phone_number LIKE '___-%';

Here, three underscores match the first three characters exactly, and the fourth must be a hyphen.

Case Sensitivity Nuances

Case sensitivity with LIKE is a common pitfall.

  • MySQL / MariaDB: Default collation is usually case-insensitive (utf8mb4_general_ci). LIKE 'A%' matches 'apple'.
  • PostgreSQL: LIKE is case-sensitive by default. Use ILIKE for case-insensitive searches.
  • SQL Server: Depends on the column collation (e.g., SQL_Latin1_General_CP1_CI_AS is case-insensitive).
  • Oracle: LIKE is case-sensitive.

Best Practice: Explicitly define collation or use functions like LOWER() / UPPER() if you need guaranteed behavior across environments It's one of those things that adds up. That alone is useful..

-- Guaranteed case-insensitive search
WHERE LOWER(column_name) LIKE '%search_term%';

Note: Applying functions to columns (LOWER(column)) often prevents the database from using indexes, leading to full table scans.

Finding Position: Locating the Character Index

Often, you need to know where a character appears, not just if it appears. This is crucial for substring extraction, data parsing, or conditional logic. Unfortunately, function names differ wildly here It's one of those things that adds up. And it works..

SQL Server: CHARINDEX

Returns the starting position (1-based index) of a substring. Returns 0 if not found.

-- Syntax: CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
SELECT CHARINDEX('@', email) AS at_position
FROM users;
-- Usage in SUBSTRING to extract domain:
SELECT SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email)) AS domain
FROM users;

MySQL / MariaDB: LOCATE / INSTR

Both return the 1-based position. LOCATE allows an optional start position; INSTR does not. Note the argument order difference!

  • LOCATE(substr, str, [pos])
  • INSTR(str, substr)
-- MySQL
SELECT LOCATE('.', domain_name) AS first_dot_pos
FROM websites;

-- INSTR (argument order reversed compared to LOCATE)
SELECT INSTR(domain_name, '.') AS first_dot_pos
FROM websites;

PostgreSQL: POSITION / STRPOS

  • POSITION(substr IN str): Standard SQL syntax.
  • STRPOS(str, substr): PostgreSQL specific, faster but non-standard.
-- Standard SQL
SELECT POSITION('.' IN domain_name) FROM websites;

-- Postgres specific
SELECT STRPOS(domain_name, '.') FROM websites;

Oracle: INSTR

Oracle's INSTR is the most powerful variant, accepting four arguments: INSTR(string, substring, position, occurrence). This allows finding the 2nd or 3rd occurrence directly without complex workarounds Worth knowing..

-- Find the 2nd occurrence of '.'
SELECT INSTR('www.example.com', '.', 1, 2) FROM dual; -- Returns 11

Advanced Pattern Matching with Regular Expressions

When wildcards (%, _) are insufficient—such as validating email formats, finding phone numbers with variable formatting, or extracting specific alphanumeric codes—Regular Expressions (Regex) are required.

MySQL / MariaDB: REGEXP / RLIKE

MySQL 8.0+ uses ICU regex libraries. REGEXP and RLIKE are synonyms Small thing, real impact..

-- Find product codes starting with 'SKU' followed by 4 digits
SELECT product_code
FROM products
WHERE product_code REGEXP '^SKU[0-9]{4}
New In

Hot off the Keyboard

You'll Probably Like These

From the Same World

Thank you for reading about Search For Character In String Sql. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home