SQL Server Count Characters in String

This SQL Server tutorial will teach you about SQL Server count characters in string.

You will learn about the two methods, LEN() and DATELENGTH(), to count the number of characters in the given string.

Table of Contents

SQL Server Count Characters in String

Counting characters in a string means checking the length of the given string. Suppose you have the string ‘USA’. How many characters does this string contain? By looking at the string, there are three characters. That’s what you will learn in this section, but with the help of the function in SQL Server.

Counting the characters in a string serves several important purposes when storing the data in a field with character limits ( like passwords), validating the input for that field to meet these conditions is important.

Counting the characters ensures the data follows the specified rules, thus preventing errors and maintaining the data quality.

Sometimes, you need to make reports or show the data on the user interfaces; in that case, formatting strings to a specific length is very necessary. So, counting the characters helps align text and padding strings with spaces. Truncating the strings to a specified length ensures consistency.

In data analysis, the length of the strings can be used as an insightful metric. For example, analyzing customer feedback stored in the database can be categorised quickly based on the length of detailed feedback and brief remarks.

From the several purposes, you know counting the characters in a string can be helpful in different ways. Next, let’s see which function in SQL Server can help count the characters in a given string.

SQL Server Count Characters in String using the LEN() function

LEN() function returns the length of any string, including spaces at the beginning of that string and the end spaces. As you know, characters can be letters, special characters, spaces, etc.

So, you can count the number of characters in a string using the LEN() function. For example, take a string ‘Counting Characters’ and pass the string to the LEN() function to count the number of characters excluding the trailing spaces.

So use the below query.

SELECT LEN('Counting Characters') CountingCharacters;

SQL Server Count Characters in String using the LEN() function

From the above query output, the number of characters in the string ‘‘Counting Characters’ is 19, including the space between two words.

Now, let’s see how to apply the LEN() function on the table column to count the characters in the string in that column.

For example, you have an Employees table with columns EmployeeID, FirstName and LastName shown below.

SQL Server Count Characters in String using the LEN function with Table

Suppose you are managing an employee database. In that database, you have the above table, and you need to ensure that the employee’s first and last name in the table does not exceed 12 characters for standardization.

So, to check that, use the below query.

SELECT EmployeeID, FirstName, LastName 
FROM Employees
WHERE LEN(FirstName + LastName) > 12;

SQL Server Count Characters in String using the LEN() function with Employees Table

When you execute the above query, it finds the employee’s name that contains more than 12 characters in their name. As you can see, nine employees have more than characters in their names.

For example, look at the employee with an ID equal to 9; the full name is ‘Atticus Tylor’; if you count the number of characters in that name by looking, you get 13 characters. Employees with more than 13 characters need to revise their names.

However, one drawback of the LEN() function is that it does not count the trailing spaces. So, where you will be required to count the spaces ( a kind of character) at the end of the string, the LEN() function will not work.

This is how you can apply the LEN() function on a string in SQL Server count characters in string.

SQL Server Count Characters in String using DATALENGTH()

As you have learned, you can count the number of characters in a string using the LEN() function but excluding trailing spaces.

Here, you will use the DATELENGTH() function to count the number of characters in a string with spaces, and it doesn’t matter where there is space in the given string; it counts all the spaces and returns the number of characters (length).

For example, pass the string ‘ characters ‘ with leading and trailing spaces to the DATELENGHT() function using the below query.

SELECT DATALENGTH(' characters ') AS CountingAllCharacters;

SQL Server Count Characters in String using DATALENGTH()

The number of characters in a string ‘ characters ‘ is 12, the length returned by the DATELENGTH() function. As you can see, the string contains the spaces at the beginning and end.

Also, you can apply the DATELENGTH() on the table column containing the string value to count the number of characters. For example, let’s use the employee table you used in the above section.

Ensure the number of characters in the employee’s full name does not exceed 16 characters using the query below.

SELECT EmployeeID, FirstName, LastName 
FROM Employees
WHERE DATALENGTH(FirstName + LastName) > 16;

SQL Server Count Characters in String using DATALENGTH Function on Table

As you can see, there are eighteen employees with more than 16 characters in their names. But as you know, the DATALENGTH() function also counts the spaces, and the above result is based on including all the spaces in the employee’s name.

But how will you count the number of characters in a string without spaces? here is a little trick: first, use the REPLACE() function to remove the spaces from the string.

For example, first remove the space from the string using the REPLACE() function within the DATELENGHT() function, following the queries below.

First, count the number of characters in the string ‘ SQL Server’ with spaces using the below query.

SELECT DATALENGTH('  SQL Server');

The number of characters in the string ‘ SQL Server’ is 12, including the spaces. Remove the spaces using the REPLACE() function and pass that value to DATALENGTH(), as shown in the query below.

SELECT DATALENGTH(REPLACE('  SQL Server',' ',''));

SQL Server Count Characters in String using DATALENGTH with REPLACE() Function

After removing the spaces, you can see the total number of characters is 9 in that string. So whenever you want to count characters in a string without spaces, first you can use the REPLACE() function to remove those spaces, then use any of the functions LEN() or DATALENGTH() to count the number of characters.

This is how to take the string in SQL Server Count characters in string.

Conclusion

In this SQL Server tutorial, you learned how to count the characters in a string using the LEN() and DATELENGTH() functions in SQL Server. Also, you learned how to use the REPLACE() function with the DATELENGTH() function to count the characters in a string, excluding all the spaces in that string.