SQL String Functions: Four Common Examples

Formulas & functions · Updated November 22, 2026

Four useful SQL string functions are UPPER, LOWER, CONCAT, and a length function such as LENGTH. They change letter case, join text, and measure text, but exact names and behavior vary by database system—SQL Server, for example, uses LEN rather than LENGTH.

What SQL string functions do

String functions operate on character data: values stored in columns such as names, addresses, product descriptions, and codes, or text written directly into a query. They can return a transformed value without changing the stored data. For example, selecting UPPER(first_name) displays a capitalized version in the query result, but does not update the first_name column.

There is no single official list of exactly four SQL string functions. Database products provide many functions, and their names and details are not perfectly uniform. The four above are a practical starting set because they cover common tasks: standardizing case, combining pieces of text, and checking how long a value is. Other frequently used functions include TRIM, SUBSTRING, REPLACE, and CHAR_LENGTH.

1. UPPER: convert text to uppercase

UPPER returns a version of a string with lowercase letters converted to uppercase. It is useful for displaying codes consistently or preparing text for a case-insensitive comparison. For example, a query can show all country codes in a consistent style even if the source data contains values such as us, Us, and US.

With a table named customers and a text column named city, this expression returns uppercase city names:

SELECT UPPER(city) AS city_upper FROM customers;

The function changes the result of the expression, not the table. To permanently store transformed values, a separate UPDATE statement is needed, and that should be done only when changing the original data is intended. Case conversion for accented characters and other writing systems can depend on the database’s character set, collation, and locale.

2. LOWER: convert text to lowercase

LOWER works in the opposite direction: it converts uppercase letters in a string to lowercase. This can make text easier to compare or create a consistent display format. For example:

SELECT LOWER(email) AS normalized_email FROM customers;

This produces lowercase values in the result set. It does not guarantee that every system treats two differently capitalized email addresses as equivalent; that is a separate matter of application rules and database collation. Applying LOWER to both sides of a comparison can provide a simple case-normalized check, but it may affect index use unless an appropriate functional or computed-column index exists. The exact indexing options depend on the database.

3. CONCAT: join text values

CONCAT combines two or more text values into one string in systems that support that form of the function. For example, if first_name contains Ada and last_name contains Lovelace, the expression below returns Ada Lovelace:

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

The space is included as a separate argument because joining two values directly would produce AdaLovelace. Concatenation is useful for labels, formatted addresses, and readable output, but it is usually better to keep separate facts in separate database columns. A full name assembled for display, for instance, should not replace the original first- and last-name fields if the application needs them independently.

Null handling deserves attention. In several common systems, CONCAT treats a null argument as an empty string, but behavior can depend on the database and its settings. A null first name may therefore yield a result that looks like a partial name rather than a null value. Check the rules for the database in use, and consider an explicit COALESCE expression when the intended treatment must be clear.

4. LENGTH, LEN, and character counts

A length function reports how long a string is, but the function name and what it counts vary. PostgreSQL, MySQL, and SQLite commonly provide LENGTH; SQL Server provides LEN; Oracle provides LENGTH. A basic example in a system that supports LENGTH is:

SELECT LENGTH('database') AS character_count;

The result is 8. In SQL Server, use SELECT LEN('database') AS character_count; for the corresponding simple result. SQL Server’s LEN does not count trailing spaces, which can surprise someone checking fixed-width or padded values. In SQL Server, DATALENGTH reports bytes used rather than the same kind of character count, so it is not a direct replacement in every comparison.

Byte length and character length are not always equal. A character outside basic English letters may require multiple bytes in a Unicode encoding. MySQL, for example, distinguishes LENGTH, which counts bytes, from CHAR_LENGTH, which counts characters. If a requirement concerns visible characters, use the database’s character-counting function and verify its behavior for the data type and encoding involved. Length checks are useful for validation, but a maximum permitted length may also be enforced by the column definition or an application rule.

Putting the four functions together

String functions can be nested inside one another, so the result of one can become the input to another. Suppose a report needs a lowercase, combined name and a count of the resulting characters. A database with the relevant function names might allow an expression like this:

SELECT LOWER(CONCAT(first_name, ' ', last_name)) AS full_name, LENGTH(CONCAT(first_name, ' ', last_name)) AS name_length FROM customers;

The first expression combines the two name fields with a space and lowercases the result. The second combines the fields and measures the resulting string. If the name is Ada Lovelace, the displayed full_name is ada lovelace and its character count is 12, including the space. For SQL Server, replace LENGTH with LEN. Repeating the concatenation makes the example easy to read, though a common table expression or subquery may be preferable when the same expression is more complicated.

Differences between SQL databases

SQL is a language standard, but database vendors implement features with variations. Before copying a string expression between systems, confirm the function name, argument rules, return type, and treatment of nulls and spaces. The most obvious difference in this group is length: LEN in SQL Server versus LENGTH in many other systems.

UPPER and LOWER are available in most mainstream relational databases, although details for locale-sensitive characters can differ. CONCAT is supported by products including SQL Server, MySQL, and PostgreSQL, but the number of accepted arguments and null behavior may vary by product or version. Some databases also support a concatenation operator, but its symbol and behavior are not universal. Avoid assuming that a query written for one product will run unchanged on another.

When portability matters, keep expressions simple, consult the documentation for the target database, and test boundary cases such as null values, empty strings, trailing spaces, and non-ASCII characters. If an application supports multiple database engines, it may need a small database-specific adaptation layer rather than relying on one expression everywhere.

Common mistakes and how to avoid them

  • Using the wrong length function: A query that works with LENGTH elsewhere may fail on SQL Server. Confirm the product and use its supported function.
  • Assuming a transformed result updates a row: A function in a SELECT statement changes only the returned expression. It does not rewrite stored data.
  • Forgetting spaces or separators: Concatenating two names without an explicit separator joins the text directly. Add a space or punctuation as a separate argument where needed.
  • Ignoring nulls: A missing value can affect a concatenated result. Decide whether missing parts should be omitted, replaced with empty text, or cause the whole result to be null.
  • Confusing bytes with characters: Encoded text can use multiple bytes per character. Choose a function that measures the quantity the requirement actually specifies.
  • Expecting case conversion to normalize every comparison: Collations and locale rules influence comparisons and case mapping. Test the data and database settings that matter to the application.

Choosing the right function for the task

Choose based on the result you need rather than memorizing function names in isolation. Use UPPER or LOWER to change the case of returned text; use CONCAT to assemble text; and use the appropriate length function to measure it. If the task is to remove unwanted spaces, look at TRIM or the database’s related left- and right-trimming functions. To extract part of a value, a substring function is more suitable. To replace one portion with another, use REPLACE.

For frequent searches or joins on normalized text, think beyond the function itself. Calling a function on a column inside a filter can prevent an ordinary index from being used efficiently in some databases. Possible alternatives include storing normalized values, creating a suitable computed or expression index, or using a case-insensitive collation. Which choice is appropriate depends on the database, query workload, and data rules; measure performance rather than assuming one option is always best.

Frequently Asked Questions

What are four string functions in SQL? Four common examples are UPPER, LOWER, CONCAT, and a string-length function. The length function is named LEN in SQL Server and is often named LENGTH in other systems.

Is CONCAT a string function? Yes. It joins text values and returns a combined string in database systems that support CONCAT. Check the system’s documentation for its argument and null-handling rules.

What is the difference between LENGTH and LEN? They serve a similar purpose but belong to different database implementations and can have different details. SQL Server uses LEN, which excludes trailing spaces; other systems use LENGTH with product-specific behavior.

Do SQL string functions change the data in a table? Not when used in a normal SELECT expression. They produce a calculated result for the query. Changing stored values requires a data-modification statement such as UPDATE.

Can SQL string functions handle NULL values? They can, but the result depends on the function and database. Concatenation in particular has differing null rules, so test the target system or use an explicit null-handling expression such as COALESCE.

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.