This tutorial will show how to use the LEN function in SQL Server 2008 R2. The LEN function returns the number of characters in an input string, excluding trailing blanks. Using the LEN function to find the number of characters in a string is important when checking data for a certain amount of characters or simply organizing data by the string length.

Setting Up

In this example we will be using a database called Sports and a table called SportsTeams. The SportsTeams table has three columns: Name, City, and TeamState. Each column has a data type of varchar is and is not null. To insert data into the table, we use the INSERT INTO, SELECT, and UNION statements. The INSERT INTO statement specifies which table and columns to insert data into. The SELECT statement is the actual data being put into the table and the UNION statement simply prevents duplicate values from occurring.

The LEN Function

Now that we have data in the SportsTeams table, we can create a query that uses the LEN function to retrieve and output the number of characters in the Name column. We start off by using the SELECT statement to retrieve the Name column and also call the LEN function on the Name column and give it a column alias of “Length”. We then use the FROM statement to specify the SportsTeams table.

Output

Execute the query and a table with two columns, Name and Length, will be outputted. As you can see, the Name column has rows for each team in the SportsTeams table with the length of the team name in the Length column.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.

Download Source Files