String Functions in SQL: Common Uses, Syntax, and Examples

Formulas & functions · Updated November 22, 2026

SQL string functions work with text: they can join values, change letter case, remove unwanted spaces, extract part of a string, or find and replace text. The exact function names and behavior vary by database system, so check whether you use PostgreSQL, MySQL, SQL Server, Oracle, or another dialect before copying a query.

What SQL string functions do

A string is a sequence of characters, such as a person’s name, an address, a product code, or a sentence. String functions let a query transform or inspect that text while reading data, returning a calculated value without necessarily changing the stored row. For example, a report can display a name in uppercase, or a filter can look for a word inside a description.

Functions are used in expressions in places such as SELECT, WHERE, and ORDER BY. A typical expression applies a function to a column or literal value:

  • UPPER(name) returns a case-adjusted version of a name.
  • TRIM(code) removes specified leading or trailing characters, with spaces as the usual default.
  • SUBSTRING(description, 1, 20) returns a portion of a description in systems that use that argument form.

The result is usually a text value, though a function that measures a string returns a number. String functions are not a single identical package across SQL products: the SQL language has standards, but database vendors also provide their own syntax and behavior.

Joining strings and changing letter case

CONCAT joins text values. For example, with a first name in first_name and a last name in last_name, a query may produce a display name with CONCAT(first_name, ' ', last_name). This adds a space between the values. Without the explicit space argument, the result would run the names together.

Some systems also support the concatenation operator ||, while SQL Server commonly uses + for concatenation. These alternatives can differ in how they handle NULL values, so they are not always interchangeable with CONCAT. Another useful function, where available, is CONCAT_WS: it joins values using a separator, such as a space or comma. Its handling of NULL arguments also depends on the database.

UPPER and LOWER convert letters to uppercase or lowercase. They can help standardize data for display or comparison, as in LOWER(email). Case conversion is not the same as permanently cleaning the database: it changes the expression returned by the query, not the stored value, unless the result is used in an update.

Applying a function to a column in a search condition can also affect performance. For example, a normal index on email may not be usable for every query written as WHERE LOWER(email) = 'sam@example.com'. The options depend on the database and schema; they can include storing normalized values or creating an expression or computed-column index.

Trimming and replacing text

TRIM is commonly used to remove extra whitespace at the edges of a value. For instance, TRIM(' blue ') returns 'blue' in systems with the usual trim behavior. Leading and trailing spaces can enter a database through imports, forms, or manual entry, and may make two values that look alike fail an exact comparison.

Some databases provide separate functions such as LTRIM and RTRIM for removing characters from the left or right side. The exact syntax for trimming a chosen character differs by product and version, so consult the relevant documentation before relying on optional arguments. Trimming generally concerns the ends of a string; it does not remove spaces between words.

REPLACE substitutes occurrences of one substring with another. For example, REPLACE(phone, '-', '') removes hyphens from a phone-number string in systems that accept an empty string in that position. It does not automatically make the result a valid phone number, and punctuation other than hyphens remains. Replacement may be case-sensitive or case-insensitive depending on the database and its collation rules.

These functions can be useful when preparing output, but cleaning data at query time does not necessarily repair the underlying records. If many applications need the same normalized value, it may be better to establish a consistent rule for storing or validating the data.

Extracting part of a string

SUBSTRING returns characters from part of a string. In MySQL and SQL Server, a common form is SUBSTRING(value, start, length); for example, SUBSTRING('DATABASE', 1, 4) returns 'DATA'. In PostgreSQL, the equivalent can be written as SUBSTRING('DATABASE' FROM 1 FOR 4). These examples use positions beginning at 1, but check your product’s documentation for the exact accepted syntax and edge cases.

LEFT and RIGHT are convenient where supported: they return a requested number of characters from the beginning or end of a value. For example, LEFT('INV-2048', 3) returns 'INV'. They are often clearer than a substring expression when the goal is simply to take a prefix or suffix.

Extraction is useful for parsing text with a dependable structure, such as taking a known prefix from a code. It is less reliable when the input format varies. A fixed-position rule that works for one postal code or identifier may silently return the wrong part when a different format appears. If the data has distinct components, storing them in separate columns is often easier to validate and query.

Finding text and matching patterns

To test whether a value contains a particular sequence, SQL offers pattern matching with LIKE. The percent sign (%) matches any sequence of characters, including an empty sequence, and the underscore (_) matches a single character. For example, WHERE name LIKE 'Jo%' selects names beginning with “Jo” in systems where the comparison rules support that match. Whether matching is case-sensitive depends on the database and collation.

When a position number is needed, use the search function provided by the dialect. PostgreSQL supports POSITION(substring IN string); SQL Server uses CHARINDEX(substring, string); MySQL provides LOCATE(substring, string); and Oracle provides INSTR(string, substring). These functions do not all use identical argument order or return the same value when there is no match. Verify those details before using the result in calculations.

A common related question is how to search for a literal percent sign or underscore with LIKE. Because those characters are wildcards, escape them using an escape character and an ESCAPE clause. The precise literal pattern depends on the chosen escape character and SQL dialect. This matters when searching user-entered text that may contain wildcard symbols.

NULL values, spaces, and character length

NULL means a value is missing or unknown; it is not the same as an empty string or a string containing spaces. String functions may return NULL when given NULL, but concatenation behavior varies. For example, MySQL’s CONCAT returns NULL if an argument is NULL, while PostgreSQL’s concat treats NULL arguments as empty strings. SQL Server’s CONCAT also converts NULL arguments to empty strings. Do not assume one system’s rule applies to another.

When a missing value should be treated as blank, use an explicit fallback such as COALESCE(column_name, ''), subject to the target system’s type rules. To find missing values, use IS NULL, not a comparison such as column_name = NULL. These distinctions help avoid surprising results in concatenated labels and filters.

Length functions require similar care. SQL Server’s LEN counts characters but excludes trailing spaces; DATALENGTH reports the number of bytes used. MySQL’s LENGTH reports bytes, while CHAR_LENGTH reports characters. PostgreSQL’s LENGTH counts characters in a string. With multibyte text such as many accented characters or emoji, a character count and byte count can differ. Choose the measure that answers the question you actually have.

Why SQL dialect and collation matter

SQL is implemented by different database products, and a function name that exists in one may have different syntax or semantics in another. Even common functions can differ in argument order, whether a length argument is optional, what happens with NULL, and how empty strings are treated. A query written for one system may need changes before it runs correctly elsewhere.

Collation is another important factor. A collation defines comparison and sorting rules for text, including aspects of case and accents. It can affect whether LIKE, equality comparisons, or replacement operations treat uppercase and lowercase letters as equivalent. Database settings and column-level collations may differ within the same product, so test with representative values rather than assuming all text comparisons behave alike.

Unicode support also deserves attention when a database stores names and text in multiple languages. A visible character may be represented by more than one code point, and a function that counts code points may not match what a person perceives as one displayed character. If exact character boundaries matter, check your database’s Unicode behavior and test strings containing combining marks and emoji.

Practical choices and common mistakes

Choose a string function based on the task and the shape of your data, not just on which syntax looks shortest. A query that transforms every row may be fine for a small report but costly over a large table, particularly when its search condition prevents ordinary index use.

  • For display formatting, use functions such as CONCAT, UPPER, or TRIM in the query, while remembering that the stored value remains unchanged.
  • For fixed-format codes, use extraction functions only after validating that the format is consistent.
  • For searching, decide whether you need exact equality, a wildcard pattern, or the position of a substring.
  • For data-quality rules used repeatedly, consider normalization at input time or an appropriate database design rather than repeating fragile cleanup expressions.

One important safety rule: do not build SQL statements by directly joining untrusted user input into a query string. String concatenation is not a substitute for parameterized queries. Use the parameter-binding mechanism provided by your programming language or database driver; it keeps input values separate from SQL syntax and helps prevent SQL injection. If a user must supply a column name or sort direction, validate it against an allowlist because identifiers generally cannot be handled as ordinary value parameters.

Frequently Asked Questions

What are the most common string functions in SQL?
Common choices include CONCAT for joining text, UPPER and LOWER for letter case, TRIM for edge whitespace, SUBSTRING for extraction, and REPLACE for substitution. Names, syntax, and behavior can vary by database.

Are SQL string functions the same in every database?
No. PostgreSQL, MySQL, SQL Server, Oracle, and other systems differ in function names, argument syntax, NULL handling, length measurements, and text comparison rules. Check documentation for the database and version that will run the query.

How do I join two strings in SQL?
CONCAT(value1, value2) is a common approach, and some systems also support an operator such as ||. Use a separate space or punctuation argument when needed, and check how the chosen method handles NULL values.

What is the difference between LENGTH and LEN?
It depends on the database. SQL Server’s LEN excludes trailing spaces, while MySQL’s LENGTH counts bytes and CHAR_LENGTH counts characters. Do not compare these names without checking their product-specific definitions.

Do string functions change the data in a table?
Not when they are used in a regular SELECT expression: they calculate values for the query result. Stored data changes only when you run a data-modification statement, such as an UPDATE, that writes a new value.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.