Ian Klosowicz
String functions are how you clean, reshape, and extract meaning from text columns in SQL. Product names, email addresses, free-text inputs, status codes, URLs, customer-entered fields -- messy string data is in almost every real dataset, and knowing how to handle it in the query layer saves you from doing it manually in a spreadsheet later.
This guide covers the string functions that come up most often in actual analyst work: the ones worth knowing cold, not a complete reference of everything that exists.
Cleaning text columns is a big part of the SQL analysts actually write on the job, so these functions come up constantly in real work.
The most basic string functions convert text to a consistent case. These matter more than they look like they do. User-entered data is inconsistent -- "New York", "new york", "NEW YORK", and "new York" are all the same city but won't match in a JOIN or a GROUP BY unless you normalize the case first.
SELECT
LOWER(email) AS normalized_email,
UPPER(country) AS country_code,
INITCAP(full_name) AS display_name -- capitalizes first letter of each word
FROM users;
Database support notes:
The most common use case I see in real work is normalizing email addresses before a JOIN. Email fields are notorious for mixed-case entries, and if you don't LOWER() both sides of the join condition, you'll drop matches silently.
TRIM removes leading and trailing whitespace (or specific characters) from a string. It's the single most-used string function for data cleaning, because whitespace in text fields is invisible and causes comparison failures that are very hard to debug.
-- Remove leading and trailing spaces
SELECT TRIM(' hello world ') AS cleaned; -- returns 'hello world'
-- Remove only leading spaces
SELECT LTRIM(' hello') AS left_trimmed;
-- Remove only trailing spaces
SELECT RTRIM('hello ') AS right_trimmed;
PostgreSQL and SQL Server also support removing specific characters instead of just spaces:
-- PostgreSQL: remove specific characters from both ends
SELECT TRIM(BOTH '.' FROM '...hello...') AS trimmed; -- returns 'hello'
-- SQL Server: TRIM with characters (SQL Server 2017+)
SELECT TRIM('.' FROM '...hello...'); -- returns 'hello'
A pattern I use constantly in pipeline work: wrap TRIM and LOWER together on any string column you're joining on or grouping by. It's cheap and prevents a category of bugs that are annoying to trace.
-- Defensive normalization before a GROUP BY
SELECT
TRIM(LOWER(product_category)) AS category,
COUNT(*) AS orders
FROM orders
GROUP BY TRIM(LOWER(product_category));
LENGTH returns the number of characters in a string. It's useful for validation, filtering, and spotting data quality issues in text fields.
-- PostgreSQL / MySQL / Snowflake / Redshift
SELECT LENGTH(phone_number) AS phone_len FROM contacts;
-- SQL Server uses LEN (not LENGTH)
SELECT LEN(phone_number) AS phone_len FROM contacts;
-- BigQuery supports both LENGTH and CHAR_LENGTH
SELECT CHAR_LENGTH(phone_number) AS phone_len FROM contacts;
Common uses in analysis:
Note: LENGTH in PostgreSQL counts bytes, not characters, for multibyte encodings. CHAR_LENGTH (or CHARACTER_LENGTH) counts actual characters. For ASCII data they're identical; for Unicode text with non-ASCII characters, use CHAR_LENGTH if character count is what you need.
CONCAT joins 2 or more strings together. This comes up when building display labels, constructing composite keys, assembling URLs, or creating full names from first and last name columns.
-- Standard CONCAT
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
-- CONCAT_WS (concat with separator) -- cleaner for multi-part joins
SELECT CONCAT_WS(', ', city, state, country) AS location FROM addresses;
Database-specific syntax:
-- PostgreSQL: || operator works natively
SELECT first_name || ' ' || last_name AS full_name FROM users;
-- SQL Server: + operator for strings
SELECT first_name + ' ' + last_name AS full_name FROM users;
-- or use CONCAT (SQL Server 2012+)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
CONCAT handles NULLs differently from the || operator. CONCAT ignores NULLs and treats them as empty strings. The || operator in PostgreSQL returns NULL if any operand is NULL. Use CONCAT when NULL in one part shouldn't break the whole expression; use COALESCE alongside || when you want explicit NULL handling.
-- Safe full name even if middle_name is NULL
SELECT CONCAT(first_name, ' ', COALESCE(middle_name || ' ', ''), last_name) AS full_name
FROM users;
SUBSTRING extracts a portion of a string by position. LEFT and RIGHT are shortcuts for grabbing characters from either end.
-- SUBSTRING(string, start_position, length)
SELECT SUBSTRING('hello world', 7, 5); -- returns 'world'
-- LEFT: first N characters
SELECT LEFT('2026-09-12', 4) AS year_part; -- returns '2026'
-- RIGHT: last N characters
SELECT RIGHT('2026-09-12', 2) AS day_part; -- returns '12'
Database syntax differences:
-- PostgreSQL: SUBSTRING or SUBSTR
SELECT SUBSTRING('hello world' FROM 7 FOR 5);
SELECT SUBSTR('hello world', 7, 5);
-- SQL Server: SUBSTRING (same as standard)
SELECT SUBSTRING('hello world', 7, 5);
-- BigQuery: SUBSTR
SELECT SUBSTR('hello world', 7, 5);
A common analyst use case: parsing structured codes. Many systems encode information in fixed-position strings -- a product SKU where characters 1-3 are the category code, characters 4-6 are the warehouse, and so on. SUBSTRING lets you extract those fields without needing a dedicated column for each one.
-- Parse a structured SKU: 'ELC-WH1-0042'
SELECT
sku,
LEFT(sku, 3) AS category_code, -- 'ELC'
SUBSTRING(sku, 5, 3) AS warehouse_code, -- 'WH1'
RIGHT(sku, 4) AS item_number -- '0042'
FROM products;
REPLACE swaps every occurrence of a substring with another string. TRANSLATE (where available) does character-by-character substitution across a mapping.
-- REPLACE: swap one substring for another
SELECT REPLACE('hello world', 'world', 'analyst'); -- returns 'hello analyst'
-- Remove unwanted characters (replace with empty string)
SELECT REPLACE(phone_number, '-', '') AS clean_phone FROM contacts;
SELECT REPLACE(REPLACE(price_text, '$', ''), ',', '') AS clean_price FROM products;
REPLACE is a workhorse for data cleaning: stripping currency symbols, removing formatting characters, standardizing delimiters. Chaining multiple REPLACEs is the standard approach when you need to remove several different characters.
TRANSLATE maps individual characters to replacements in a single pass, which is more efficient than chaining REPLACEs when you have many single-character substitutions:
-- PostgreSQL / Oracle / Snowflake: TRANSLATE
-- Replace 'a' with '1', 'e' with '2', 'i' with '3'
SELECT TRANSLATE('aeiou', 'aei', '123'); -- returns '123ou'
-- Remove specific characters entirely (no replacement character)
SELECT TRANSLATE(phone_number, '()-. ', ' ') FROM contacts;
-- (then REPLACE the spaces, or use REGEXP_REPLACE)
SQL Server doesn't have TRANSLATE natively before SQL Server 2017. MySQL doesn't have it at all. For those environments, chain REPLACEs or use a regex function.
These functions find where a substring appears within a string, returning its position as an integer. They're most useful combined with SUBSTRING when you need to extract a variable-length piece of a string up to a delimiter.
-- PostgreSQL / MySQL / Snowflake / Redshift: POSITION
SELECT POSITION('@' IN email) AS at_sign_pos FROM users;
-- 'ian@analysthive.io' returns 4
-- SQL Server: CHARINDEX
SELECT CHARINDEX('@', email) AS at_sign_pos FROM users;
-- MySQL also supports INSTR
SELECT INSTR(email, '@') AS at_sign_pos FROM users;
The real power comes from combining POSITION with LEFT or SUBSTRING to extract the part of a string before or after a delimiter:
-- Extract the domain from an email address
-- PostgreSQL
SELECT
email,
SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;
-- SQL Server
SELECT
email,
SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email)) AS domain
FROM users;
Returns 0 (or NULL in some databases) when the substring isn't found, so always test your assumptions before applying this in a WHERE clause or a derived column at scale.
LIKE filters rows based on a text pattern. The 2 wildcards are % (matches any sequence of characters) and _ (matches exactly one character).
-- Emails from a specific domain
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Names starting with 'J'
SELECT * FROM customers WHERE last_name LIKE 'J%';
-- 5-character codes
SELECT * FROM products WHERE sku LIKE '_____';
-- Contains a substring anywhere
SELECT * FROM notes WHERE body LIKE '%refund%';
ILIKE (PostgreSQL) is the case-insensitive version. In other databases, combine LIKE with LOWER:
-- PostgreSQL: case-insensitive match
SELECT * FROM users WHERE email ILIKE '%@GMAIL.COM';
-- Other databases: normalize before matching
SELECT * FROM users WHERE LOWER(email) LIKE '%@gmail.com';
Performance note: LIKE with a leading wildcard ('%something') can't use an index on the column and forces a full table scan. On small tables this doesn't matter. On large tables it becomes a serious performance problem. If you need full-text search at scale, that's what full-text index capabilities are for -- LIKE is not the right tool.
The fastest way to internalize these is to run them against real data, and there are where to practice these for free environments built for exactly that.
Splitting a delimited string into parts is less standardized than other string operations, and the approach varies significantly by database.
PostgreSQL: STRING_TO_ARRAY and SPLIT_PART
-- Split on delimiter and get the Nth part
SELECT SPLIT_PART('2026-09-12', '-', 1) AS year; -- returns '2026'
SELECT SPLIT_PART('2026-09-12', '-', 2) AS month; -- returns '09'
SELECT SPLIT_PART('2026-09-12', '-', 3) AS day; -- returns '12'
-- Convert delimited string to array
SELECT STRING_TO_ARRAY('a,b,c', ','); -- returns ARRAY['a','b','c']
BigQuery: SPLIT
SELECT SPLIT('a,b,c', ',')[OFFSET(0)] AS first_element; -- returns 'a'
Snowflake: SPLIT_PART and STRTOK
SELECT SPLIT_PART('2026-09-12', '-', 1) AS year;
SELECT STRTOK('a,b,c', ',', 2) AS second_token; -- returns 'b'
SQL Server: STRING_SPLIT (returns a table)
SELECT value FROM STRING_SPLIT('a,b,c', ',');
-- Returns rows: 'a', 'b', 'c'
When splitting strings is a recurring need for a column, that's often a signal the schema should store those values in a separate table rather than as a delimited string. Repeated parsing in SQL is a workaround, not a long-term solution. That said, you'll encounter delimited strings in real data constantly, so knowing the tools matters.
COALESCE returns the first non-NULL value from a list of expressions. For string work it's the standard way to replace NULLs with a default value or fall back from one column to another.
-- Replace NULL with a default label
SELECT
user_id,
COALESCE(preferred_name, first_name, 'Unknown') AS display_name
FROM users;
-- Standardize empty strings and NULLs together
SELECT
COALESCE(NULLIF(TRIM(phone_number), ''), 'not provided') AS phone
FROM contacts;
NULLIF converts a specific value to NULL, which is useful as the inverse of COALESCE. NULLIF(expression, value) returns NULL if the expression equals the value, otherwise returns the expression. The NULLIF(TRIM(col), '') pattern is the standard way to treat empty strings the same as NULLs, since SQL treats them as different values by default.
-- Turn empty strings into NULLs before aggregating
SELECT
COUNT(*) AS total_rows,
COUNT(NULLIF(TRIM(notes), '')) AS rows_with_actual_notes
FROM support_tickets;
In practice, cleaning a messy string column usually requires several functions in sequence. Chaining them is standard -- you read from the inside out.
-- Normalize a user-entered city name
SELECT
INITCAP(TRIM(LOWER(city))) AS clean_city
FROM addresses;
-- Clean a phone number down to digits only
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone, '(', ''), ')', ''), '-', ''), ' ', ''), '+', '') AS digits_only
FROM contacts;
-- Extract username from email and title-case it
SELECT
INITCAP(SPLIT_PART(LOWER(TRIM(email)), '@', 1)) AS username_display
FROM users;
When a chain gets long enough to be hard to read, move it into a CTE or a computed column in a staging layer so downstream queries stay clean:
WITH cleaned_contacts AS (
SELECT
contact_id,
TRIM(LOWER(email)) AS email,
INITCAP(TRIM(LOWER(full_name))) AS full_name,
REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', '') AS phone_digits
FROM raw_contacts
)
SELECT *
FROM cleaned_contacts
WHERE email LIKE '%@%';
I structure data pipelines this way at work -- one CTE for raw cleaning, then query business logic against the clean version. It separates the messy string work from the actual analysis and makes both layers easier to test and change independently.
If you want to see how this fits into building real portfolio projects that show up in interviews, Analyst Hive walks through it step by step.
What's the difference between CHAR_LENGTH and LENGTH in SQL?
For standard ASCII text they return the same number. The difference shows up with multibyte character sets (UTF-8, Unicode). LENGTH in PostgreSQL counts bytes; CHAR_LENGTH counts characters. A Chinese character encoded in UTF-8 is 3 bytes but 1 character. For English-language data this distinction rarely matters. For international data, use CHAR_LENGTH if you need the character count.
How do I remove all special characters from a string in SQL?
The cleanest approach is REGEXP_REPLACE, which uses a regular expression to match a pattern and replace it. Most modern databases support it: REGEXP_REPLACE(column, '[^a-zA-Z0-9]', '', 'g') removes everything except letters and numbers. If REGEXP_REPLACE isn't available, chain multiple REPLACE calls for each character you want to remove.
Is LIKE case-sensitive in SQL?
It depends on the database and collation. PostgreSQL's LIKE is case-sensitive; use ILIKE for case-insensitive matching. MySQL's behavior depends on the column collation -- the default is case-insensitive for most setups. SQL Server depends on the collation of the database. The safe approach that works everywhere: LOWER(column) LIKE LOWER(pattern).
How do I check if a string contains a substring in SQL?
Use LIKE with wildcards: WHERE column LIKE '%substring%'. Or use POSITION/CHARINDEX and check if the result is greater than 0: WHERE POSITION('substring' IN column) > 0. For case-insensitive checks, normalize with LOWER first or use ILIKE in PostgreSQL.
Can I use string functions in a WHERE clause?
Yes, and it's common. WHERE LOWER(email) LIKE '%@domain.com' or WHERE TRIM(status) = 'active' are standard patterns. The performance note: applying a function to an indexed column in a WHERE clause often prevents the index from being used. If the table is large and performance matters, consider storing the normalized value as a separate column.
What's the best way to concatenate strings with possible NULLs?
Use CONCAT instead of the || operator. CONCAT treats NULL as an empty string; || propagates NULL (the whole expression becomes NULL if any part is NULL). If you want CONCAT behavior with || in PostgreSQL, wrap each nullable part in COALESCE: COALESCE(col, '') || ' ' || COALESCE(col2, '').
String functions are the data cleaning layer of SQL. TRIM and LOWER run on almost every text column before a JOIN or GROUP BY. REPLACE handles the most common formatting issues. SUBSTRING and LEFT/RIGHT extract fixed-position fields. LIKE handles pattern matching. COALESCE and NULLIF manage NULLs and empty strings.
Learn to chain them. Most real data cleaning is 3 to 5 functions nested together, and once you get comfortable reading and writing those chains, a lot of data quality problems become quick fixes instead of spreadsheet detours.
If you're building SQL skills toward a first data analyst role, join Analyst Hive. The program covers the SQL that shows up in actual analyst jobs and applies it in portfolio projects that demonstrate the kind of thinking employers are looking for.
