String manipulation is something you will likely find yourself doing at some point in your career as a database professional. Luckily, SQL Server has many great system functions we can use to make the task of string searching and manipulation very easy.
In this week’s tutorial, we’ll cover the TOP 10 SQL Server string functions you should know. These are the top 10 functions I see and use regularly in my career as an application developer. We’ll discuss how they work and provide examples for you to better understand.
We’ll discuss the following:
- SUBSTRING
- CHARINDEX
- LEN
- LTRIM and RTRIM
- UPPER and LOWER
- CONCAT
- REPLACE
- REPLICATE
- Tips, tricks, and links
For your convenience, I condensed this entire tutorial into a FREE 1-page guide:
Download your FREE 1-page Simple SQL Cheat Sheet on the Top 10 String Functions you NEED to know!
This guide will definitely be a great resource for you to reference throughout your career as a database professional. Make sure you download it today!
Let’s get into it:
1. SUBSTRING
We use SUBSTRING to return a section of a larger string. The syntax is simple:
SUBSTRING(<expression>, <starting-position>, <number-of-characters-to-read>)
As an example, let’s say we have the string ‘Spaghetti Noodles’. If we wanted to capture and return just the word ‘Spaghetti’ within that, we would start at position 1 and read a total of 9 characters (since that’s the length of the word “Spaghetti“). This is exactly what we specify in the SUBSTRING function:
We basically look at a larger string and return a substring from that. Easy enough!
The <expression> can be many things. It can be a string literal, like in the example, or maybe a column name, or the return value from a scalar function, whatever! It just needs to be some kind of character string expression. In fact, this is true for all the string functions discussed in this tutorial.
2. CHARINDEX
We use CHARINDEX to basically locate a string within a string. The syntax is as follows:
CHARINDEX(<string-to-look-for>, <string-to-look-within>)
For example, we can use CHARINDEX to locate the word ‘Ham’ within the larger string ‘Green Eggs and Ham’. CHARINDEX will return the starting position of the word ‘Ham’ if it is indeed within the larger string. The statement would look like the following:
If the string you’re searching for simply doesn’t exist in the larger string, the function returns 0:
You might think returning zero could introduce some confusion. You might ask yourself, “If my CHARINDEX call returns 0, how do I know if that means the substring wasn’t found or if the substring was found and it’s at the very beginning of the larger string?”
A fine question, indeed. You’re missing this one tid-bit of information: Character positions are 1-based, not 0-based.
In other words, the first character in a string is always at position 1. If you come from a programming background, you might think the starting character is at position 0 because that’s how arrays work. But we need to throw that thinking out the window when it comes to SQL Server.
An example of using SUBSTRING and CHARINDEX together
To give you another example of how CHARINDEX can help us, we’ll create a simple table called Employees and populate it with some simple information:
CREATE TABLE Employees
(
EmplID INT IDENTITY,
EmployeeName VARCHAR(50)
)
INSERT INTO Employees (EmployeeName)
VALUES
('Thompson, Emily'),
('Warren, Tim'),
('Smith, Greg'),
('Tanner, Susan')
Here’s what the table should look like:
Notice the format of our employee names is LastName, FirstName. Think about how we would write a query to return only an employee’s last name.
Take a look at the following query, then we’ll discuss it:
We start with the SUBSTRING function. The expression we use in the SUBSTRING function is simply the EmployeeName column. The substring we want to gather from this column is every character from position 1 thru where the comma is.
That last part is a bit tricky. The comma can certainly be at different positions depending on how long the person’s last name is. So we can’t just hard code the number of characters to read in our SUBSTRING function.
Instead, we can use CHARINDEX. We use CHARINDEX to very simply tell us the location of the comma within the EmployeeName value. That position indicates the number of characters to read for the SUBSTRING function. In fact, it needs to stop reading one position before that to make sure we exclude the comma in the final result set. That’s why we need to do a simple ‘– 1‘ at the end.
How would you change this query to return only an employee’s first name? Let me know in the comments!
CHARINDEX has an optional third parameter
The last thing I need to point out about CHARINDEX is how it has an optional third parameter. It is simply an integer that tells the function at what position within the larger string to start looking for the smaller string.
Take a look at the following example:
If you know your substring isn’t going to be within the first X characters of the larger string, you can specify X as a third parameter and save SQL Server some time. In this example, we’ve told SQL Server to look for the word ‘Kitty’ within the larger string starting at position 70. This likely saved SQL Server a bit of time!
3. LEN
The LEN string function is an easy one. It very simply returns the number of characters in a string. Take a look at this example:
Simple enough. One thing you need to know about LEN is how it excludes trailing spaces. Take a look at the return value if we add several spaces to the end of our string:
It’s still 4! If for some reason you wanted to count those spaces, you should use DATALENGTH instead:
4. LTRIM and RTRIM
Speaking of spaces, we can use LTRIM and RTRIM to return a string where the leading/trailing spaces are removed, respectively.
If we wanted to remove all those trailing spaces from our previous example, we can use RTRIM:
To prove this output does not include trailing spaces, we could use the handy DATALENGTH function we learned about in the last example:
Yep, those trailing spaces are gone!
Take a look at the following string with leading spaces:
It’s much more obvious to see the leading spaces in this output. Of course, if we wanted to remove those leading spaces, we would use LTRIM:
5. UPPER and LOWER
These are also very simple string functions. They simply return your input string in all uppercase or all lowercase, respectively:
These can be really helpful for when you need to feed the output of your query to something that is case sensitive. Maybe the program consuming the result of your query expects values that are all in lower case. In that scenario, you could use LOWER to make sure your string is provided that way.
6. CONCAT
This is another easy function. It very simply concatenates (a.k.a. combines) string values to produce one large string value. Take a look at this silly example:
Notice you can concatenate many strings together. You must have at least 2 strings, but no more than 254.
7. REPLACE
Just like the name sounds, REPLACE will replace a substring within a larger string with a different string. The updated version of the larger string is then returned to the user.
Here’s the syntax:
REPLACE(<string-expression>, <string-to-look-for>, <replacement-string>)
Take a look at this silly example:
Pretty slick.
If the string expression does not contain the substring we’re looking for, the original string expression is returned, unmodified.
8. REPLICATE
Finally, the REPLICATE function is a fun one. It just repeats the string expression X number of times. Here’s the syntax:
REPLICATE(<string-expression>, X)
Here’s an example of repeating the phrase ‘Hello there. ‘ many times:
Your X value can be any integer value, including a BIGINT value which has a maximum of about 9 quintillion, which actually isn’t as large as it seems….
Just kidding, that’s an absolutely massive number. You could travel to Saturn and back 100 times and still not even be close to 9 quintillion miles traveled. Isn’t science fun?
9. Tips, tricks, and links
There’s just one tip I can offer about SQL Server string functions:
Tip # 1: Get used to the idea of using a string function within a string function
There were a few examples where we used a string function within another string function, like when we used CHARINDEX to help with the SUBSTRING function. This is a very common thing to do when it comes to string manipulation. It’s rare that string values and character positions will always be the same, for example, which means we usually need to derive that information. The best way we can derive things like starting positions, ending positions, substrings, etc. is to use other string functions.
If you understand all the string functions discussed in this tutorial, I’m confident you’ll be able to use that knowledge to manipulate your strings and extract the information you need to get the job done!
Links:
You should consider getting the book T-SQL Fundamentals by Itzik Ben-Gan. It goes over several core concepts you should know about Microsoft SQL Server, including a whole section on string system functions. This book helped me out tremendously when I was first starting out with SQL Server, and I reference it all the time now. You won’t regret owning this book, trust me. Get it today!
Next Steps:
Leave a comment if you found this tutorial helpful!
Don’t forget to download you FREE 1-page guide:
FREE 1-page Simple SQL Cheat Sheet on the Top 10 String Functions you NEED to know!
This guide will definitely be a great resource for you to reference throughout your career as a database professional. Make sure you download it today!
Do you know how the CAST and CONVERT system functions work? Click the link to find out!
Also, make sure you check out the full beginner-friendly tutorial on another important querying topic: Window Functions:
SQL Server Window Functions: An introduction for beginners Â
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
