How to Use String Functions in SQL?

When working with Microsoft SQL Server, you’ll often need to work with text, whether it’s combining names, cleaning up messy data, or pulling out one part of the string. Knowing how to use string functions in SQL makes these tasks much easier.

In this post, we’ll look at some of the most useful SQL string functions. I will show you how to concatenate two columns, how to concatenate with separator, how to concatenate multiple columns, how to use replace in SQL, and how do I use substring in SQL.

Here is a list of string functions that we will cover:

Now, let me show you some practical examples.

SQL Concat Two Columns

If you want to combine two text columns into one, you can use the CONCAT() function:

SELECT CONCAT(FirstName, LastName) AS FullName
FROM Employees

This joins the first and last name directly, but without a space between them:

SQL Concat Multiple Columns

You can concatenate multiple columns the same way you did with two of them:

SELECT CONCAT(EmployeeID, FirstName, LastName) AS FullName
FROM Employees

Here is what it looks like in SQL Server Management Studio:

SQL Concat with Separator

You’ll usually want a space, comma, or other separator between the values. With CONCAT(), you can simply add it as another argument:

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employees

Here is what it looks like in SSMS:

If a column contains NULL, CONCAT() treats it as an empty string, so you won’t get unwanted “NULL” text in your result.

How to Use REPLACE in SQL

The REPLACE() function is perfect for cleaning up data. It replaces all occurrences of a given string with another string.

Example: Removing dashes from phone numbers:

SELECT CustomerID,Phone,REPLACE(Phone, '-', '') AS CleanPhone
FROM Customers

This takes something like 123-456-7890 and changes it to 1234567890. The query above will show you both the original phone number and a clean one, so you can see the difference:

How Do I Use SUBSTRING in SQL?

The SUBSTRING() function lets you extract part of a string. You provide the starting position and the number of characters you want.

Here is an example of extracting the first three letters of a City from Employee table:

SELECT EmployeeID,SUBSTRING(City,1,3) AS CityShortName
FROM Employees

This returns only the first three characters from City field:

Putting It All Together

Here’s an example that combines multiple string functions in one query:

SELECT 
	EmployeeID
	,CONCAT(FirstName, ' ', LastName) AS FullName
	,REPLACE(HomePhone, '-', '') AS CleanPhone
	,SUBSTRING(City,1,3) AS CityShortName
FROM Employees

This query:

• Combines first and last name into a single column (FullName)
• Cleans up phone numbers by removing dashes
• Extracts a three-character code prefix from City

Final Thoughts

Once you know how to use string functions in SQL, you can clean, format, and combine your text data in almost any way you need. We covered:

• SQL concat two columns
• SQL concat with separator
• SQL concat multiple columns
• How to use replace in SQL
• How do I use substring in SQL
• How to combine multiple string functions in one SQL query

These functions are simple, but incredibly powerful when used together.

If you want to find out more about SQL, make sure you check my previous blog post on Data Types in Microsoft SQL Server. If you want to learn about other SQL Server string functions, visit this link here.

If you would like to learn more about programming SQL queries, make sure you buy this book: T-SQL Fundamentals (Developer Reference)