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, whileLOWER(text)converts them to lowercase. - Remove surrounding spaces:
TRIM(text)removes leading and trailing spaces in systems that support this form. Some systems also offerLTRIMandRTRIMfor one side at a time. - Measure text:
CHAR_LENGTHor a database-specific alternative returns a character count. Functions that count bytes are different and can return another result for multibyte text. - Extract text:
SUBSTRINGselects 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, andREPLACElocate 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
CONCATor 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 useINSTR; SQL Server usesCHARINDEX. - Concatenating: PostgreSQL supports
||andCONCAT; SQL Server supportsCONCATand, in appropriate contexts,+; MySQL supportsCONCATand has a separateCONCAT_WSfunction. - Counting length: PostgreSQL and many other systems provide
CHAR_LENGTHorLENGTH, while SQL Server commonly usesLENfor character counts andDATALENGTHfor bytes. - Extracting substrings:
SUBSTRINGis 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
- Excell Battery Company: What It Makes and Who It Serves
- Enum Values: Meaning, Examples, and How They Work
- Balance GraphQL Checklist for Accurate API Design
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Counting Unique Values in Excel: Formulas and MethodsTo count distinct, nonblank values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) when your values are in A2:A100.…
- Excel Formula for File Path: Get the Workbook LocationIn Excel for Microsoft 365 on Windows, use =CELL("filename",A1) to return the saved workbook’s path, file name, and…
- How to Group the Same Numbers in Excel: Sort and CountTo group identical numbers in Excel, sort the entire data range by the column containing those numbers: select…
- Excel Calculation for Hours Worked: Formulas and TipsFor a shift with start time in A2, end time in B2, and unpaid break duration in C2,…
- How to Add a Sequence of Dates in Excel: 3 Easy WaysTo add a sequence of dates in Excel, enter a starting date and the next date in two…
- Transpose in Excel Using a Formula: Step-by-Step GuideTo transpose a range in Microsoft Excel for Microsoft 365, switch its rows and columns with the TRANSPOSE…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.