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():

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:

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:

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:

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:

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.

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:

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:

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

In SQL Server, you can also use +:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.