CodeRang
SQL topics

SQL String Functions with Practical Examples

SQL string functions transform, extract, compare, and normalize text inside queries. This example shows text filtering and normalization while introducing common operations such as LIKE, substring extraction, trimming, case conversion, and concatenation.

Updated · SQL Arena team

Regex courseAll problems

What you will practice

  • Normalize text with LOWER and TRIM
  • Split and extract text with SPLIT_PART and SUBSTRING
  • Match patterns with LIKE and ILIKE
  • Combine row values with STRING_AGG
  • Handle NULL correctly when concatenating strings

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Find repeated domains in customer contact data

The contact_notes table contains customer contact strings. Email values may have leading or trailing spaces and inconsistent letter case. Extract the part after @, normalize it, and return only domains that occur at least twice. The result must contain the normalized domain and its number of matching rows as contacts.

Query 1PostgreSQLv16
SELECT
  LOWER(TRIM(SPLIT_PART(contact_text, '@', 2))) AS domain,
  COUNT(*) AS contacts
FROM contact_notes
WHERE contact_text ILIKE '%@%'
GROUP BY 1
HAVING COUNT(*) >= 2
ORDER BY contacts DESC, domain;
Submit
Resultcustomerscontact_notes
Answer accepted

How SQL string functions work

SPLIT_PART(contact_text, '@', 2) splits each value around @ and returns the second part. For alice@example.com, that part is example.com. SPLIT_PART is a good fit when the delimiter is known and the structure is predictable. SUBSTRING handles a different family of problems: it can extract text by position and length or by a matching rule, so it is useful when there is no simple delimiter.

TRIM removes unwanted whitespace around the extracted domain, and LOWER makes case differences irrelevant. Without normalization, Example.com, EXAMPLE.COM, and example.com followed by a space would become separate GROUP BY values. Normalizing before comparison is usually clearer than trying to repair grouped results later. The WHERE clause uses ILIKE to keep values that contain @; in PostgreSQL, ILIKE is the case-insensitive counterpart of LIKE.

GROUP BY collects identical normalized domains, HAVING removes groups with only one row, and ORDER BY gives the output a stable order. Other SQL text exercises often require STRING_AGG to join values from several rows into one delimited string. Concatenation has its own edge case: with the || operator, a NULL component can turn the whole expression into NULL. COALESCE or CONCAT can be used when optional pieces need explicit handling.

Common SQL string functions handle tasks such as trimming whitespace, converting text to upper or lower case, extracting substrings, measuring length, and combining values. LIKE performs pattern matching with two common wildcards: % matches any sequence of characters, while _ matches one character. A pattern such as '%sql%' therefore finds values containing sql somewhere inside the string. Function names and concatenation syntax differ between SQL dialects, and case sensitivity can also vary. When processing nullable text, check how the selected concatenation operator or function handles NULL values.

SQLPostgreSQL v16
SELECT
  LOWER(TRIM(SPLIT_PART(contact_text, '@', 2))) AS domain,
  COUNT(*) AS contacts
FROM contact_notes
WHERE contact_text ILIKE '%@%'
GROUP BY 1
HAVING COUNT(*) >= 2
ORDER BY contacts DESC, domain;
Query result
domaincontacts
example.com3
news.io2
work.org2

Common SQL string function mistakes

01

Grouping raw text before cleaning it

Different casing and whitespace produce different grouping keys even when the text means the same thing. Apply LOWER, TRIM, or other normalization to the expression used for grouping or comparison.

02

Using LIKE without understanding wildcards

LIKE does not automatically mean contains. A pattern such as 'abc' matches that exact text, while '%abc%' searches for abc anywhere in the value. The underscore wildcard matches exactly one character.

03

Ignoring NULL during concatenation

The || operator in PostgreSQL can produce NULL when one component is NULL. If a text fragment is optional, decide whether it should become an empty string, a replacement value, or cause the whole result to remain NULL.

Practice problems on this topic

Regex course

Once simple normalization feels comfortable, practice text problems that require different operations rather than repeating the same split. Free Easy exercises in SQL Arena include masking a card number, checking phone numbers with 11 digits, extracting promo codes after a label, and identifying ISO-formatted dates. More advanced exercises work with payment history, navigation paths, and text masking. When SPLIT_PART and SUBSTRING are no longer enough for the pattern you need, the “Regular Expressions” course is a natural next step.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What are SQL string functions?

SQL string functions are functions used to inspect or transform text values. Common operations include trimming whitespace, changing letter case, extracting substrings, calculating length, replacing text, and concatenating strings.

How does LIKE work with SQL strings?

LIKE matches a string against a pattern. The % wildcard matches any number of characters, while _ matches exactly one character.

How do I extract a substring in SQL?

Use the substring function or equivalent syntax provided by your SQL dialect. It can typically extract text starting at a position and optionally limit the number of returned characters.

How do I get the length of a string in SQL?

SQL databases provide functions for measuring string length, although the function name and whether it counts characters or bytes can vary by dialect.

How do I concatenate strings in SQL?

Depending on the SQL dialect, strings can be combined with a concatenation operator or a concatenation function. NULL handling may differ between those approaches, so it should be checked when nullable columns are involved.

See also

Practice SQL string functions

SQL Arena contains 1,192 automatically checked exercises, with separate sandboxes for PostgreSQL, MySQL, ClickHouse, and BigQuery. The free tier includes 292 exercises without a card, and the “Regular Expressions” course is available when basic string operations are no longer enough.

Practice SQL string exercises

More practice from CodeRang