SQL String Functions: Syntax and Practical Examples

Formulas & functions · Updated November 22, 2026

SQL string functions let you inspect, combine, clean, and transform text values in a database. Common examples include LOWER, UPPER, TRIM, SUBSTRING, REPLACE, and concatenation; exact names and behavior can vary between database systems such as PostgreSQL, MySQL, SQL Server, and Oracle.

What SQL string functions do

A string is a sequence of characters stored in a text column or supplied as a text value. SQL string functions operate on those values, often returning a new string or a numeric result such as a character count or position. They can be used in a query’s SELECT list, in a filter, or as part of an expression used for sorting or grouping.

For example, a customer table might contain names with inconsistent capitalization or spaces. A query can apply TRIM to remove surrounding spaces and LOWER to standardize letter case for display or comparison. A different function can extract a portion of an account code, replace a character, or join a first and last name into one value.

These functions transform values while a query runs; they do not normally change the stored data. To permanently clean a table, an application or an UPDATE statement must write the transformed value back. It is useful to distinguish temporary presentation and comparison from a deliberate change to the underlying records.

Common types of string functions

Although each database has its own function set, most relational systems provide ways to perform several familiar text operations:

  • Change letter case: UPPER(text) converts letters to uppercase, while LOWER(text) converts them to lowercase.
  • Remove surrounding spaces: TRIM(text) removes leading and trailing spaces in systems that support this form. Some systems also offer LTRIM and RTRIM for one side at a time.
  • Measure text: CHAR_LENGTH or a database-specific alternative returns a character count. Functions that count bytes are different and can return another result for multibyte text.
  • Extract text: SUBSTRING selects part of a string, generally using a starting position and, in many systems, a requested length.
  • Find or replace text: Functions such as POSITION, INSTR, CHARINDEX, and REPLACE locate or substitute text, but their names and argument order differ by database.
  • Join text: Concatenation combines strings. Depending on the system, it may use a function such as CONCAT or an operator such as ||.

Function calls can be nested. For instance, a query could trim a value and then convert it to lowercase. The inner operation runs on the original value first, and the outer operation uses the result. Nesting is convenient, but a long chain can be harder to read and maintain than a clearly named expression or a staged query.

Examples for cleaning and transforming text

The following examples show common patterns. They use familiar SQL syntax, but check the documentation for your database before relying on a function’s exact spelling or edge-case behavior.

To return email addresses without accidental spaces and with lowercase letters, a query can use:

SELECT LOWER(TRIM(email)) AS normalized_email
FROM customers;

This changes the values in the query result only. It does not update the email column. Also, lowercasing can be useful for a particular matching rule, but the correct treatment of email addresses can depend on the application and its requirements.

To extract a section of a code, use SUBSTRING. In many SQL systems, positions start at 1 rather than 0. For example, if a product code is AB-4721, extracting four characters from position 4 returns 4721:

SELECT SUBSTRING(product_code, 4, 4) AS item_number
FROM products;

The three-argument form is common, but syntax varies: some systems allow a form using FROM and FOR, and SQLite uses the function name substr. Confirm the rules for the database in use, especially if the query must run on more than one system.

To replace every occurrence of a particular text sequence, a common pattern is:

SELECT REPLACE(phone_number, '-', '') AS digits_only
FROM contacts;

Here the hyphens are removed from the returned value. This example is not a complete phone-number validation method: real phone data can contain spaces, parentheses, country codes, or other punctuation.

For combining values, many databases support CONCAT. In systems that accept multiple arguments, this can join a first name, a space, and a last name:

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

Oracle’s CONCAT function takes two arguments, so an Oracle query would need nested calls or its concatenation operator. This is one reason to test examples in the target database rather than assuming every SQL product accepts identical syntax.

How syntax differs between databases

SQL is a language standard, but database products implement features at different times and add their own functions. Even when two systems offer the same operation, the function name, argument order, treatment of missing values, or supported options may differ. A query written for one product may need adjustment before it works elsewhere.

  • Finding a character: PostgreSQL supports POSITION(substring IN string); MySQL and Oracle commonly use INSTR; SQL Server uses CHARINDEX.
  • Concatenating: PostgreSQL supports || and CONCAT; SQL Server supports CONCAT and, in appropriate contexts, +; MySQL supports CONCAT and has a separate CONCAT_WS function.
  • Counting length: PostgreSQL and many other systems provide CHAR_LENGTH or LENGTH, while SQL Server commonly uses LEN for character counts and DATALENGTH for bytes.
  • Extracting substrings: SUBSTRING is widely available, but accepted syntax and optional arguments are not identical across products.

These are examples, not a complete compatibility table. A database’s version matters too: newer releases may add functions or syntax that older installations do not have. For portable SQL, use widely supported features where practical, document the target database, and run tests against every supported database engine.

NULL values, spaces, and character encoding

A frequent source of surprising results is NULL. It represents an unknown or absent value, not an empty string. Many string expressions that receive NULL return NULL, but concatenation rules are not uniform. For example, the || operator in PostgreSQL returns NULL if either side is NULL, while the behavior of concatenation functions can differ. SQL Server’s CONCAT treats NULL arguments as empty strings.

If missing values should be treated as empty text, make that choice explicit with COALESCE, which is broadly supported. For example, a query can concatenate COALESCE(first_name, '') with another value. That is a data rule, not merely a formatting fix: an absent name and a genuinely empty name may have different meanings in an application.

Whitespace is another issue. TRIM usually addresses spaces at the ends of a value, not every kind of whitespace in every character set. Tabs, line breaks, nonbreaking spaces, and other Unicode characters may require additional handling. Similarly, a length function may count characters, bytes, or behave according to a particular data type. A visible character can take multiple bytes in a Unicode encoding, so a byte count is not necessarily the number of characters a person sees.

Case conversion and comparisons can also depend on collation and locale rules. Some languages have case mappings that do not behave like simple English uppercase and lowercase conversion. If text is used for identity checks, sorting, or search, use the database’s documented collation behavior and the application’s matching requirements rather than assuming case conversion solves every comparison problem.

Performance and filtering

A string function in a SELECT expression is often straightforward, but applying a function to a column in a filter can affect how the database uses an index. For example, a condition like WHERE LOWER(email) = 'sam@example.com' may prevent a conventional index on email from being used efficiently, depending on the database, its optimizer, and available indexes.

Possible approaches include storing a normalized value, creating a functional or computed-column index where the database supports it, or using a collation that provides the needed comparison behavior. Each choice has design and maintenance consequences. Inspect the execution plan and measure the query with realistic data instead of assuming that a function always makes a query slow or that an index will always be ignored.

For very large tables, avoid transforming every row unnecessarily. If a query only needs a formatted label in its output, do the transformation in the result expression. If it needs repeated text matching, consider how the data is stored and indexed. Full-text search is a separate capability designed for searching words and language-oriented text; simple string functions are not a substitute for it.

Common mistakes and safe use

String functions are useful for shaping text, but they are not a security boundary. Do not build SQL statements by concatenating untrusted user input into query text. That can allow SQL injection, and applying TRIM, REPLACE, or another string function to the input does not reliably prevent it. Use parameterized queries or prepared statements provided by the database driver.

Other common mistakes include assuming positions start at zero, confusing characters with bytes, and expecting a cleaning query to modify stored records. Developers also sometimes rely on one database’s concatenation or NULL behavior when the application may later use another database. Make the intended rule explicit and test representative cases, including missing values, empty strings, leading and trailing spaces, punctuation, and non-English text.

When a function produces an unexpected result, check three things: the database product and version, the input data type and collation, and the function’s documented behavior for NULL and edge cases. These checks usually reveal whether the issue is syntax, data, or an assumption about portability.

Frequently Asked Questions

What are string functions in SQL?
They are functions and operators that work with text values. They can change case, trim spaces, extract characters, find or replace text, count length, and combine strings. The available names and details depend on the database system.

What is the most commonly used SQL string function?
There is no single function that is most common in every application. CONCAT, LOWER, UPPER, TRIM, and SUBSTRING are frequent choices for combining or preparing text, but the right one depends on the task and SQL dialect.

How do I join two strings in SQL?
Use the concatenation method supported by your database. Many systems provide CONCAT(value1, value2); some also support an operator such as ||. Check how the chosen method handles NULL values before using it in production.

Does SQL use zero-based positions in SUBSTRING?
Many common SQL implementations count the first character as position 1, not position 0. Details can vary by function or product, so consult the documentation for the database you use and test the starting position with a short example.

Do string functions change the data in a table?
No, using a function in a query generally changes only the value returned by that query. To store a transformed value, use an intentional data update or handle normalization when writing records, with suitable validation and safeguards.

Are SQL string functions the same in every database?
No. Many systems have functions with similar purposes, but names, argument order, supported syntax, and behavior can differ. Check the target system’s documentation when writing portable SQL or moving a query between database products.

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.