String Functions: Beginner

Store and query text values correctly

Databases offer different string types for different use cases. VARCHAR stores variable-length text, using only the space needed. CHAR stores fixed-length text, padding shorter values with spaces. The choice affects storage and comparison behavior.

An empty string '' is a string with zero characters. It is not the same as missing data. Missing data is represented as NULL. An empty string is a value. NULL means no value exists.

User 2 provided an empty string, meaning "I have no middle name." User 3 provided NULL, meaning "I did not answer this question." The distinction changes how you filter and count these rows.

SELECT
  order_id,
  status
FROM orders
WHERE status ___ ___

String literals must be enclosed in single quotes. Double quotes have a different meaning in SQL, typically referring to column or table names rather than text values.

Understanding how strings are stored and compared is the foundation for all the string functions you will learn in the following sections.

Combine text from multiple columns

CONCAT is short for concatenate, which means to join or link together. CONCAT combines multiple strings into a single string by placing them end-to-end in the order you specify. The function accepts two or more arguments and returns one continuous string.

The syntax is CONCAT(string1, string2, string3, ...). Each argument is a string, either a column or a literal value. The function joins them sequentially without adding spaces unless you explicitly include them.

The CONCAT function is straightforward to use but has important behaviors to understand when working with separators and multiple values.

CONCAT can join many values to build formatted output. Consider generating display text for a product listing:

Here, CONCAT combines five pieces: product name, a separator, category, an opening parenthesis with dollar sign, and the price. The result is a formatted string ready for display.

Beyond basic two-string joins, CONCAT handles complex scenarios involving many values, mixed types, and potentially missing data.

CONCAT accepts as many arguments as you need. Each argument is evaluated and converted to a string, then joined in order. You can mix column names and literal strings freely:

If any argument to CONCAT is NULL, most databases will return NULL for the entire result. This behavior can cause unexpected blank output when fields contain missing data.

Bob's middle_name is NULL, so the entire concatenation becomes NULL. To handle this, you can use COALESCE to replace NULL with an empty string, or use conditional logic to skip NULL values.

Knowing when and where to use CONCAT helps you write cleaner queries and maintain consistency across your data pipelines.

CONCAT is ideal for building composite strings from multiple data sources in your database.

Display Names

Build full names from separate first and last name fields

Unique Identifiers

Join keys to create composite identifiers

Formatted Strings

Combine addresses, URLs, file paths, or email addresses

Dynamic Messages

Construct labels and messages from data values

Use CONCAT when you need to combine strings in the database rather than in application code. This approach centralizes logic, reduces data transfer, and makes transformations visible in your queries.

SELECT
  ___(___, ' ', ___) AS full_name
FROM users

Understanding how CONCAT processes its arguments helps you predict behavior with NULL values and mixed types.

Measure the size of text values

LENGTH returns the number of characters in a string. It counts every character, including letters, numbers, spaces, and punctuation. The function accepts a single string argument and returns an integer.

The syntax is LENGTH(string). The result is the count of characters, not bytes. For most English text, character count equals byte count, but for international characters or emojis, one character may use multiple bytes. LENGTH always returns character count.

LENGTH is one of the simplest string functions, but its utility in data validation and analysis makes it indispensable.

The function counts all characters. 'Ergonomic Keyboard' has 19 characters including the space. 'Mouse' has 5. LENGTH() is exact.

You can use LENGTH in WHERE clauses to filter rows based on string size:

This query finds usernames shorter than 5 characters. LENGTH makes it possible to enforce constraints, identify outliers, or find data that violates length requirements.

LENGTH plays a key role in identifying data quality issues. Empty strings, NULL values, and unexpectedly short or long text all reveal problems worth investigating.

LENGTH helps distinguish between empty strings and NULL values:

User 1 has a bio with 17 characters. User 2 has an empty bio with length 0. User 3 has NULL, so LENGTH returns NULL. This distinction is useful for data quality checks.

There are two common ways to check if a string has content: using LENGTH or comparing directly to an empty string. Each approach handles NULL differently.

Beyond individual row validation, LENGTH helps analyze text patterns across entire datasets and inform schema design decisions.

Combining LENGTH with aggregate functions reveals patterns in text data:

LENGTH appears throughout data validation, filtering, and analysis workflows.

LENGTH is commonly used in data quality pipelines to catch malformed input. If you expect product codes to be exactly 8 characters, you can filter WHERE LENGTH(product_code) != 8 to find invalid entries.

SELECT
  username
FROM users
WHERE ___(___) ___ 5

LENGTH is one of the fastest string functions because it only needs to scan the string once and count characters without performing any transformation or copying.

LENGTH has straightforward execution characteristics but important edge case behavior.

Standardize text to lowercase

LOWER converts all letters in a string to lowercase. Non-letter characters remain unchanged. The function accepts a single string argument and returns a new string with all uppercase letters replaced by their lowercase equivalents.

The syntax is LOWER(string). The input string is not modified; LOWER returns a new string. This function is essential for case-insensitive comparisons and data normalization.

LOWER is simple to use but essential for achieving consistent data handling across your queries.

Email addresses are often stored inconsistently due to user input. LOWER standardizes them.

This query finds all variations of 'alice', regardless of how it was entered. LOWER(username) converts each stored value to lowercase, then compares it to the lowercase search term.

Understanding how LOWER interacts with your data helps avoid surprises when working with special characters and NULL values.

LOWER does not change the original data in the table. It only transforms the output:

Use LOWER() when deduplicating data. Two records with email addresses 'user@site.com' and 'User@Site.COM' are duplicates, but string comparison treats them as different unless you normalize.

LOWER only affects letters. Numbers, spaces, punctuation, and special characters remain unchanged:

The letters became lowercase. The hyphen, digits, exclamation mark, and spaces are unchanged. LOWER() transforms only alphabetic characters.

The comparison below shows common problems caused by inconsistent casing and how LOWER solves them.

SELECT
  ___(___) AS normalized_email
FROM users

LOWER is idempotent: applying it twice produces the same result as applying it once. A single call is sufficient to normalize any string regardless of its original casing.

When comparing strings from different sources, apply LOWER() to both sides so the comparison is case-insensitive without modifying the stored data.

Convert text to uppercase for matching

UPPER converts all letters in a string to uppercase. It is the inverse of LOWER. Non-letter characters remain unchanged. The function accepts a single string argument and returns a new string with all lowercase letters replaced by their uppercase equivalents.

The syntax is UPPER(string). Like LOWER, the input is not modified; UPPER returns a transformed copy. This function is used for formatting, normalization, and case-insensitive operations.

UPPER works identically to LOWER but transforms text in the opposite direction. The same principles apply.

UPPER is commonly used to format output for display, especially for codes, labels, or emphasis.

UPPER can be combined with other functions and used in conditional logic to build powerful transformations.

Just like LOWER, you can use UPPER for case-insensitive comparisons by converting both sides to uppercase:

This query finds all orders with a status of 'pending', regardless of case. UPPER(status) converts each value to uppercase before comparing it to the uppercase search term.

Both functions serve the same purpose: case normalization. Use LOWER() for user-facing text like emails and usernames. Use UPPER() for system identifiers like codes and statuses. The choice is often a matter of convention.

UPPER can be combined with other string functions to build complex transformations:

Consistent use of UPPER for identifiers and codes helps maintain data quality across your systems.

UPPER is particularly useful for system identifiers and status codes that benefit from consistent formatting.

Format Codes

Standardize status codes, labels, and identifiers for display

Normalize for Matching

Ensure case-insensitive joins and comparisons

Deduplicate Records

Find and merge records with case variations

Use UPPER when output needs to be in uppercase for consistency, readability, or business requirements. Many systems require uppercase codes for integration with legacy platforms.

SELECT
  ___(
    ___(___),
    '-',
    order_id
    ) AS region_order
FROM orders