SQL String Functions: Syntax, Usage, and Examples
SQL string functions allow you to manipulate and extract information from text data stored in your database. Whether you’re trimming whitespace, converting case, or finding patterns, mastering these functions gives you powerful tools to clean, search, and transform strings.
How to Use SQL String Functions
You use SQL string functions in SELECT statements , WHERE clauses, and anywhere you want to work with text-based values. These functions accept one or more string arguments and return modified or calculated results.
Here’s a basic example using UPPER():
sql
SELECT UPPER(first_name) AS upper_name FROM users;
Use SQL string functions to modify how text appears in query results or to match search criteria with precision.
When to Use String Functions in SQL
SQL string functions come in handy across many types of queries and workflows. Here’s when you might reach for them.
Clean and Standardize Data
Use string functions in SQL when you want to remove leading/trailing spaces, fix inconsistent casing, or strip unwanted characters. This is common when importing messy data or preparing it for reports.
Perform Pattern Matching
Use functions like CHARINDEX, LIKE, and PATINDEX when you need to locate specific sequences of characters inside a larger string.
Extract or Transform Data
Use SQL string functions to extract parts of a string—like first names from full names or area codes from phone numbers. You can also build new string values by combining fields together.
Validate Input or Build Conditions
Use string functions inside WHERE clauses or CASE statements to validate formats, check values, or filter based on specific substrings.
Examples of Common SQL String Functions
Let’s explore examples of popular SQL string functions in action.
UPPER() and LOWER()
Use these to change the case of a string:
sql
SELECT UPPER('hello') AS all_caps, LOWER('WORLD') AS all_lower;
You might use them when comparing strings case-insensitively or for consistent formatting in reports.
LEN() or LENGTH()
Use these to count the number of characters in a string:
sql
SELECT LEN('hello world') AS char_count;
In databases like MySQL, use LENGTH() instead of LEN().
LEFT() and RIGHT()
Use these to get characters from the start or end of a string:
sql
SELECT LEFT('invoice_2024', 7) AS prefix; -- returns 'invoice'
SELECT RIGHT('invoice_2024', 4) AS suffix; -- returns '2024'
These are great for parsing IDs, filenames, or codes.
SUBSTRING()
Use this to extract a specific section of a string by position:
sql
SELECT SUBSTRING('abcdef', 2, 3) AS segment; -- returns 'bcd'
You might use this to pull out middle names, fixed-length codes, or structured data embedded in a string.
TRIM(), LTRIM(), and RTRIM()
Use TRIM() to remove spaces from both ends of a string. Use LTRIM() and RTRIM() to remove spaces just from the left or right side.
sql
SELECT TRIM(' hello ') AS cleaned;
This is useful when dealing with user input or CSV imports.
CHARINDEX() or INSTR()
Use these to find where a substring appears in another string:
sql
SELECT CHARINDEX('dog', 'hotdog bun'); -- returns 4
In MySQL, use INSTR() instead of CHARINDEX.
CONCAT() or + Operator
Use CONCAT() to combine two or more strings:
sql
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
In SQL Server, you can also use +:
sql
SELECT first_name + ' ' + last_name AS full_name;
Use this in reports, labels, or constructing email addresses.
Learn More About SQL String Functions
Nesting String Functions
You can chain or nest string functions for more complex operations. For example, to clean a string and format it for display:
sql
SELECT UPPER(TRIM(name)) AS formatted_name
FROM contacts;
This trims and capitalizes the name at the same time.
Using String Functions in WHERE Clauses
You can apply string functions directly in filtering conditions:
sql
SELECT *
FROM employees
WHERE LEFT(last_name, 1) = 'S';
This query returns all employees whose last name starts with “S”.
Or use CHARINDEX to check if a string contains a keyword:
sql
SELECT *
FROM products
WHERE CHARINDEX('eco', product_description) > 0;
Use this approach to filter based on embedded terms.
SQL Server String Functions Specifics
In SQL Server, you have access to more advanced string functions like STRING_AGG() for grouping:
sql
SELECT STRING_AGG(first_name, ', ') AS names
FROM users
WHERE city = 'Austin';
Use this to create comma-separated lists from grouped rows.
Another useful function is FORMAT() for displaying numbers or dates as strings:
sql
SELECT FORMAT(salary, 'C', 'en-US') AS formatted_salary
FROM payroll;
This converts salary numbers into U.S. currency format.
Finding and Replacing Substrings
Use REPLACE() to substitute one substring for another:
sql
SELECT REPLACE('2023-01-01', '-', '/') AS new_date;
You can use this to normalize date formats or strip out symbols.
Counting Words or Delimited Items
If your string has a known delimiter, you can count items by comparing lengths:
sql
SELECT LEN('apple,banana,pear') - LEN(REPLACE('apple,banana,pear', ',', '')) + 1 AS item_count;
Use this to find how many words or values are in a delimited string column.
Dealing With NULLs
Be aware that most SQL string functions return NULL if one of their inputs is NULL. Use ISNULL() or COALESCE() to handle these cases:
sql
SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) AS full_name
FROM customers;
Use this to avoid losing entire rows due to null values.
Formatting Output in Reports
When preparing results for reports, string functions help you format data fields into readable sentences:
sql
SELECT CONCAT('Order #', order_id, ' placed on ', CAST(order_date AS VARCHAR)) AS summary
FROM orders;
This turns raw columns into narrative-style messages.
Use Cases for SQL String Functions in Real Applications
- Creating unique usernames from first and last names
- Parsing email addresses to extract domains
- Generating search-friendly slugs from product titles
- Formatting mailing labels from name/address components
- Cleaning messy imports before inserting into the database
- Validating phone number formats before saving
Use SQL string functions anytime your data needs a little extra shaping to fit the business rules or display needs. With practice, you’ll be able to combine them fluently for advanced transformations.