Written by the

SQL: STRING FUNCTIONS

Master textual transformation in SQL: concatenation with || and CONCAT, length calculations, casing standardizations, whitespace trimming, substring slicing, pattern positioning, padding, collations, and regular expressions.

Overview of SQL String Functions

Textual data is central to relational database design, representing names, email addresses, descriptions, codes, and JSON payloads. SQL string functions provide built-in tools for formatting, sanitizing, extracting, and analyzing textual data directly inside queries.

Unlike many programming languages (such as Python, Java, or C++) that use 0-based array indexing, SQL string indexing is 1-based. The first character of every string sits at position 1.

Core Rules of SQL String Operations

  • 1-Based Indexing:In SUBSTR and INSTR, position 1 represents the initial character. Passing 0 is either invalid or coerced to 1.
  • Immutability:String functions in SELECT queries return transformed copies; they do not alter the underlying table data on disk unless used in an UPDATE statement.
  • Null Sensitivity:Most scalar string functions return NULL when passed a NULL argument, following standard three-valued SQL logic.

CONCAT and Concatenation Operator ||

Concatenation merges two or more strings end-to-end into a single combined string. SQL provides two primary ways to concatenate values:

  • Pipe Operator (||): The ANSI SQL standard concatenation operator, supported natively in SQLite, PostgreSQL, Oracle, and modern MySQL.
  • CONCAT(str1, str2, ...): A multi-argument function supported across MySQL, PostgreSQL, SQL Server, Oracle, and modern SQLite.
ApproachSyntax ExampleBehavior with NULL Values
Pipe Operator (||)first_name || ' ' || last_nameReturns NULL if any operand is NULL
CONCAT()CONCAT(first_name, ' ', last_name)Treats NULL as empty string ('') without failing
CONCAT_WS()CONCAT_WS(', ', city, country)Concatenates with separator, automatically skipping NULLs

String Concatenation in SQL

SQL

Merge text attributes and evaluate NULL handling with || and CONCAT

NULL Concatenation Safeguard

  • The || Trap:'Welcome ' || NULL evaluates to NULL. Wrap nullable fields in COALESCE(col, '') to prevent dropped outputs.
  • CONCAT Immunity:CONCAT('Welcome ', NULL) safely outputs 'Welcome ' across modern engines.

LENGTH / LEN

The length function calculates the number of characters in a string:

  • LENGTH(str): The ANSI standard function used in PostgreSQL, SQLite, MySQL, and Oracle.
  • LEN(str): Used in Microsoft SQL Server (note that LEN in SQL Server automatically strips trailing spaces).

Calculating String Lengths

SQL

Determine string length and filter product records by name length

Characters vs Bytes

  • Character Count:LENGTH() counts visible Unicode characters regardless of encoding.
  • Byte Count:Functions like OCTET_LENGTH() (Postgres) or DATALENGTH() (SQL Server) measure physical byte consumption for multibyte UTF-8 strings.

UPPER and LOWER

Case standardizations transform string characters uniformly:

  • UPPER(str): Converts all characters to uppercase.
  • LOWER(str): Converts all characters to lowercase.

These functions are commonly used to enforce case-insensitive lookups when databases default to case-sensitive collation.

Case Normalization with UPPER and LOWER

SQL

Format display text and perform reliable case-insensitive lookups

Expression Indexing for Case-Insensitive Queries

  • SARGability Impact:Searching WHERE LOWER(email) = '...' prevents normal indexes on email from being used in some engines.
  • Functional Indexes:In PostgreSQL and Oracle, create a functional index (CREATE INDEX idx ON tbl (LOWER(email))) to maintain index speed.

TRIM, LTRIM, RTRIM

Trimming functions remove unwanted whitespace (or specific custom characters) from the edges of a string:

  • TRIM(str): Removes leading and trailing spaces from both ends.
  • LTRIM(str): Removes spaces only from the left (leading) side.
  • RTRIM(str): Removes spaces only from the right (trailing) side.

Whitespace and Symbol Trimming

SQL

Demonstrate space stripping and symbol removal from string boundaries

Trimming Specific Characters

  • ANSI Syntax:TRIM(BOTH '#' FROM str) or TRIM(str, '#') strips designated characters from boundaries.
  • Fixed-Length CHAR Cleanup:Legacy databases storing CHAR(50) pad values with trailing spaces; use RTRIM() to extract clean strings.

SUBSTRING / SUBSTR

The substring function extracts a slice of text starting from a given position for a specified length:

SUBSTR(string, start_position, length)
-- ANSI Standard alternate:
SUBSTRING(string FROM start_position FOR length)

If length is omitted, the function extracts everything from start_position to the end of the string.

Substring Extraction with SUBSTR

SQL

Deconstruct formatted date strings into granular components

Negative Indexing in SUBSTR

  • Negative Offsets:In SQLite and Oracle, passing a negative starting position counts backwards from the end (e.g. SUBSTR(str, -4) grabs the last 4 characters).
  • SQL Server Note:SQL Server SUBSTRING requires all 3 arguments and does not support negative starting positions.

REPLACE Function

The REPLACE function performs a global search-and-replace operation across a target string:

REPLACE(original_string, search_pattern, replacement_string)

Every occurrence of search_pattern is substituted with replacement_string. If replacement_string is an empty string '', the matched pattern is deleted.

String Search and Replacement

SQL

Substitute substrings and clean special characters using REPLACE

Case Sensitivity in REPLACE

  • Exact Matches Only:In most SQL engines, REPLACE is case-sensitive; searching for 'com' will not match 'COM'.
  • Global Scope:REPLACE updates all occurrences in the string, not just the first match.

LEFT and RIGHT

The LEFT and RIGHT functions extract a specified number of characters from string boundaries:

  • LEFT(str, n): Returns the first n characters starting from the left.
  • RIGHT(str, n): Returns the last n characters starting from the right.
EngineLEFT(str, n) EquivalentRIGHT(str, n) Equivalent
PostgreSQL / MySQL / SQL ServerLEFT(str, n)RIGHT(str, n)
SQLite / OracleSUBSTR(str, 1, n)SUBSTR(str, -n)

Extracting String Boundaries

SQL

Retrieve prefixes and suffixes using portable substring boundary techniques

Boundary Extraction Tip

  • Cross-Database Compatibility:Writing SUBSTR(str, 1, n) and SUBSTR(str, -n) works identically across SQLite, Oracle, and PostgreSQL.

INSTR / POSITION / CHARINDEX

When you need to know where a substring begins inside a text field, SQL provides position discovery functions. If the search string is found, they return its 1-based index; if not found, they return 0.

Database EngineFunction SyntaxArgument Order
SQLite / MySQL / OracleINSTR(haystack, needle)Target string first, search term second
PostgreSQL (ANSI SQL)POSITION(needle IN haystack)Search term first, target string second
Microsoft SQL ServerCHARINDEX(needle, haystack)Search term first, target string second

Locating Substrings and Dynamic Slicing

SQL

Find separator positions and dynamically split usernames and domains

Argument Sequence Caution

  • Order Trap:Notice the parameter reversal: INSTR(haystack, needle) vs POSITION(needle IN haystack) vs CHARINDEX(needle, haystack).
  • Zero Match Handling:Always check INSTR(col, '@') > 0 before subtracting 1, otherwise SUBSTR can throw a length error.

LPAD and RPAD

Padding functions append fill characters to a string until it achieves a targeted length:

  • LPAD(str, target_length, pad_char): Pads to the left (ideal for zero-padding IDs like 00042).
  • RPAD(str, target_length, pad_char): Pads to the right (ideal for fixed-width text exports).

In SQLite, left-padding numbers with zeros is conveniently achieved using PRINTF('%05d', id), while general string padding is achieved using concatenation.

String Padding with LPAD and Formatted Patterns

SQL

Generate fixed-width codes and zero-padded identifiers

Fixed-Width Banking and Accounting Formats

  • Truncation Rule:If the input string is already longer than target_length, LPAD and RPAD truncate the string to fit target_length.
  • Default Fill:If pad_char is omitted in Postgres/Oracle, it defaults to a space character.

REVERSE

The REVERSE(str) function flips the sequence of characters in a string. Supported natively in SQL Server, MySQL, and PostgreSQL, it is frequently used to inspect strings backwards from the end (such as isolating file extensions or checking palindromes).

Reversing Character Sequences

SQL

Reverse text strings and explore backwards parsing patterns

File Extension Extraction via REVERSE

  • Reverse Trick:To find the last occurrence of a dot in 'report.final.pdf', reverse the string, search for the first dot with INSTR, and slice the suffix.

Splitting Strings

Splitting delimited text (such as CSV values 'SQL,Python,Docker') into individual rows is a common operational task:

  • PostgreSQL: UNNEST(STRING_TO_ARRAY(str, ',')).
  • SQL Server: STRING_SPLIT(str, ',').
  • SQLite: Uses a recursive CTE or json_each.

Splitting Delimited Strings into Rows

SQL

Parse delimited CSV strings into relational rows using recursive CTEs

Database Normalization Advice

  • First Normal Form (1NF):Storing delimited lists in a single column violates 1NF. Whenever possible, store many-to-many relationships in dedicated child junction tables.

String Comparison and Collation

A collation defines the algorithmic rules that govern how strings are compared and sorted in SQL. It dictates whether comparisons are case-sensitive, accent-sensitive, or language-specific.

Collation FlagComparison MeaningExample Equality
BINARY / CSCase-sensitive; compares raw byte codes'SQL' != 'sql'
NOCASE / CICase-insensitive; folds letters before comparison'SQL' = 'sql'

Collation Rules and Case-Insensitive Matching

SQL

Observe how COLLATE NOCASE enables natural case-insensitive lookups

Collation Application Levels

  • Column Definition Level:Define collation during table creation (e.g. email TEXT COLLATE NOCASE) to make all queries on that column case-insensitive by default.
  • Query-Level Override:Append COLLATE NOCASE to individual comparison operators to override table defaults on the fly.

Regular Expressions in SQL (REGEXP, SIMILAR TO)

While LIKE provides basic wildcard matching (% and _), regular expressions allow sophisticated pattern validation:

  • REGEXP operator: Supported in MySQL, SQLite, and Oracle (in PostgreSQL, the regex match operator is ~).
  • SIMILAR TO: SQL:1999 standard supported in PostgreSQL (blends LIKE wildcards with regex grouping).

Pattern Matching and Regular Expressions

SQL

Validate email formats and inspect advanced pattern matching techniques

Regex vs LIKE Index Optimization

  • Full Table Scans:Regular expressions cannot utilize standard B-Tree indexes and require full table scans across all candidate rows.
  • Prefix Optimization:If you only need to match prefixes (e.g. starting with 'ABC'), use col LIKE 'ABC%' to maintain index lookups.

Knowledge Check: SQL String Functions

1. What does concatenating a string with NULL return when using the || operator in ANSI SQL?

2. What is the starting index position of characters in SQL string functions?

3. How does the CONCAT() function handle NULL arguments differently from ||?

4. Which function locates the 1-based starting position of a substring in SQLite and MySQL?

5. How can you extract the last 4 characters of a string portably in SQLite?

6. Which collation allows case-insensitive string equality comparisons in SQLite?

PreviousNext