An Overview of Microsoft SQL Server Text Functions

Data analysts use text values when creating SQL reports. You can create your own labels with functions, classify rows based on text value, or do computations based on it. This article covers how to do all that.

The text values in databases are not just for reading. As with numeric values, we perform operations on them and manipulate the text. SQL Server has a fair number of text functions.

Functions in T-SQL, the SQL dialect used in SQL Server, are predefined pieces of code you can use in your query. They make it easy to re-use your code. What they are and how they work, including string, numeric, date and time, and other T-SQL functions, are covered in the course "Common Functions in MS SQL Server." If you want to learn about the MS SQL Server functions or simply strengthen your knowledge, the 149 interactive exercises await to teach you and test your knowledge.

It's important to know the most common T-SQL built-in functions to write effective queries. Below, I'll demonstrate how the most popular SQL Server text functions work.

If you want to have SQL Server on your machine, here are the instructions on how to install it.

The Syntax of the SQL Server Text Functions

Their general syntax is always the same: the name of the function followed by parentheses:

EXAMPLE_FUNCTION(argument1, argument 2, …)

The function accepts arguments in the parentheses. The arguments supply information about which piece of data is to be used and what the function should do with it.

The functions are usually used in the SELECT statement. So, I'll use them in the examples that way.

Using Text Functions in SQL Server

The point of these examples is not just to show you what a particular function does. I also try to use each in a relatable example to anyone in data analysis.

Sample Data

The table I will use in the examples contains data about the company's customers. Not surprisingly, the table is named customers. Its columns are:

• id – The customer's ID and the primary key (PK) of the table.
• first_name – The customer's first name.
• last_name – The customer's last name.
• identification_number – The customer's national identification number.
• phone – The customer's phone number.
• postal_code – The customer's postal code.
• email – The customer's e-mail address.

And here's the data:

CONCAT()

The CONCAT() function joins two or more strings (a string is a sequence of characters) into one string. It's probably the T-SQL text function you will use most often.

For example, you want to show the customer's first and last names in a single column. This is a typical use of the CONCAT() function: to combine the first and the last names to create a label for a row.

SELECT CONCAT (first_name, ' ', last_name) AS customer_name
FROM customers;

The arguments are separated by a comma. The first argument in the CONCAT() function is the column first_name. The next argument is the space character in single quotation marks. Without that, the first and the last names are concatenated as one word without any spaces. The third argument in the function is the customer's last name.

This is what you get with the code.

LEN()

This function is used when you want to find the number of characters in the specified text. In other words, it returns the length of a string.

You can use it, for example, to check if customer identification numbers are valid. Let's say they all have to have 12 digits. If they don't, then there is an error in the data.

SELECT id,
       identification_number,
       LEN(identification_number) AS identification_number_check
FROM customers;

FROM customers;

First, select the columns id and identification_number. To check the lengths of customer identification numbers, use the LEN() function and specify the desired column as the argument. That's it!

If you want to get more sophisticated, you can use the CASE WHEN statement.

SELECT id,
       identification_number,
       CASE WHEN
LEN(identification_number) = '12'
	   	THEN 'YES'
	   	ELSE 'NO'
	 END AS identification_number_ok
FROM customers;

Again, the LEN() function is here. This time, I use the CASE WHEN statement to return certain values based on what LEN() returns. I know a valid identification number has 12 digits, no more, no less. If it is 12 digits, then it shows "YES", meaning the identification number is OK. If it's not OK, then it shows "NO".

All the identification numbers are OK.

One of the common uses of LEN() is in a WHERE clause. For instance, you may want to find the longest last name so that you know how much space you need on a report for customer last names. The following is an example of such code.

SELECT id,
	 first_name,
	 last_name,
	 LEN(last_name) AS longest_last_name
FROM customers
WHERE LEN(last_name) = (SELECT MAX(LEN(last_name))
						FROM customers);

The LEN() function in the SELECT statement returns the length of the last name. I use a WHERE clause to show only the customer with the longest last name. The condition in WHERE specifies that the customer's last name length has to be equal to the maximum length of all last names. To get this maximum, I use the MAX() aggregate function in the subquery.

Here's the one last name that is the longest.

UPPER() and LOWER()

The UPPER() function turns all the characters in the text to uppercase. And, as you may have guessed, LOWER() does the opposite: it turns all the characters into lowercase.

Imagine that all customers get access to a mobile app. Their username is the first and the last names as one word and all in uppercase. The code below generates the usernames.

SELECT id,
	 first_name,
	 last_name,
	 UPPER(CONCAT(first_name, last_name)) AS app_login
FROM customers;

I select the ID and the customer's first and last names. Then, I use the UPPER() function. To get both first and last names as one word, I use the CONCAT() function. As you see, a text function may be nested in another. Finally, UPPER() turns the result of the concatenation into all uppercase.

Every customer also has access to a webshop. However, imagine the username there is different: it is also the customer's first and last names as one word, but this time in lowercase.

SELECT id,
	 first_name,
	 last_name,
	 LOWER(CONCAT(first_name, last_name)) AS webshop_login
FROM customers;

It's the same as in the previous example, except here, I use the LOWER() function.

SUBSTRING()

The SUBSTRING() function returns a substring, or a part, of a string. First, you specify the string from which you want to extract the substring. Second, you tell it to extract the substring beginning at the nth character of the string. Finally, you specify the length of the substring.

This function is useful for getting the customer's initials. Let's say you want to display the initials on their webshop profile. This involves using the CONCAT() and SUBSTRING() functions.

First, let me show you how SUBSTRING() works on its own. Take a look at this code:

SELECT 
  first_name,
  SUBSTRING(first_name, 2, 3)
FROM customers;

The SUBSTRING() function in the query extracts a part of the customer's first name. Which part? The number 2 as the second argument tells the function to start from the second character of the string. The third argument is the number 3, meaning that the extracted part is to be three characters long. It's easier to see in the query result:

Inspect the result carefully and compare it with the explanation I have given. The second character in the name "Anett" is "n". Start from there and count three characters – you get "net". It's the same with all other names. For Frederico, it is "red"; for Marcellina, it is "arc", and so on.

Now, to a more practical example:

SELECT id,
 first_name,
	 last_name,
	 CONCAT(SUBSTRING(first_name, 1, 1), SUBSTRING(last_name, 1, 1)) AS customer_initials
FROM customers;

To get the customer's initials, I need to extract the first letter of each of the first and the last names. Then, the result has to be concatenated.

This is why there are two SUBSTRING() functions as arguments of the CONCAT() function. The first one returns the substring starting from the first letter of the first name, and its length is one character. We do the same with the last name.

REPLACE()

This function replaces a set of specified characters appearing anywhere in a string with another set of characters you also specify.

You may want to use it to format phone numbers. The sample data shows them with "+" before the country code, but I want to replace it with "00". This is how to do it:

SELECT id,
	 first_name,
	 last_name,
	 phone,
	 REPLACE(phone, '+', '00') AS phone_formatted
FROM customers;

The first argument in the REPLACE() function is phone, the column I want to format. The second argument is the character I want to replace, which is "+". Finally, the "00" is the set of characters that replaces all occurrences of "+".

TRIM()

The purpose of this function is to delete spaces (or any other character) to the left and to the right of a text.

The formatting of data in databases is often inconsistent. This is especially true when the data is fetched from systems that allow manual entries.

Take a look at the data in the column postal_code. The data is really messy! There are spaces before it. You may not see them, but the spaces are there also after the postal codes. There's a way to clean this up:

SELECT id,
	 first_name,
	 last_name,
	 postal_code,
	 TRIM(postal_code) AS postal_code_formatted
FROM customers;

Simply specify the column in the parentheses of the function. All leading and trailing spaces are removed.

CHARINDEX()

The CHARINDEX() function searches in a string for a character or an expression. Its output is an integer representing the position of this character in the string.

As an example, let's find the position of the character "@" in the email of every customer.

SELECT id,
	 first_name,
	 last_name,
	 email,
	 CHARINDEX('@', email) AS position_of_@
FROM customers;

The function only has two arguments. The first is the character I want to look for. The second is the column in which the function looks for the specified character.

When is this of practical use? We may want to extract the username part of the customer's email address.

SELECT id,
 first_name,
	 last_name,
	 SUBSTRING(email, 1, CHARINDEX('@', email)-1) AS email_username
FROM customers;

We may think of the email address username as a substring containing some number of characters before the "@". However, this number is different for every address. This is how I get around the problem.

In the SUBSTRING() function, the first argument is the column email from which I want to extract the username. The second argument tells the function to start from the first character of the email address.

Then, for the third argument, I use the CHARINDEX() function to get the position of the "@" character. The length of the substring is its position minus one. Why minus one? Without it, the substring includes the "@", and I don't want that.

REVERSE()

The final function in this overview is REVERSE(). It reverses the order of the characters in the string so that the text looks as if you see the string in a mirror (kind of).

Let's say I want to do that with customer emails.

SELECT id,
	 first_name,
	 last_name,
	 REVERSE(email) AS reversed_mail
FROM customers;

I simply name the column in the parentheses of the function to get the desired result.

How about I make it more practical? Imagine the company wants to reward customers by giving them a stamp with a personalized message attesting to their loyalty. The message is to be neutral enough to use for fun at home or work.

SELECT id,
       first_name,
       last_name,
	 email,
	 REVERSE(CONCAT(first_name, ' ', last_name, ' is the boss!')) AS stamp
FROM customers;

The thing with stamps is that they have to be printed in reverse. The message uses the customer's first and last names, adding he/she is "the boss."

I concatenate the first and the last names to get that, adding the blank space in between. Then I add the statement " is the boss!". There must be a blank space so that this part doesn't get concatenated with the customer's name.

Now that I have the phrase, I need to reverse it so that it shows correctly when stamped out. For this, I simply nest the CONCAT() function in the REVERSE() function.

Voilà. Or should I say, "àlioV"? OK, to be fair, REVERSE() doesn’t exactly make stamps readable. Each letter also has to be backward. That is not the case here, but you get the point!

While you're at it, take a look at the Standard SQL text functions and see what the differences are compared to T-SQL.

Want Some More SQL Server Text Functions?

The eight I have covered here are more than enough to get you started in SQL Server text functions.

These examples are helpful and relatable for anyone working with data. I'm also aware they are just the tip of the iceberg. Make sure you practice them as often as possible. The same goes for other SQL Server functions.

And what better place to do that than with the course "Common Functions in MS SQL Server"? It's a part of the SQL from A to Z in the MS SQL Server learning track, which gives you a structured and comprehensive learning plan for writing queries in T-SQL.

For more practice opportunities, find out where you can find a good MS SQL Server course. We have also selected several courses from various sources that may be the best for you. Happy learning!