{"id":1147,"date":"2019-02-16T16:59:13","date_gmt":"2019-02-16T09:59:13","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1147"},"modified":"2024-03-02T21:37:33","modified_gmt":"2024-03-02T14:37:33","slug":"sql-server-len-function","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-string-functions\/sql-server-len-function\/","title":{"rendered":"SQL Server LEN() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server LEN()<\/code> function to return the number of characters of an input string.<\/p>\n\n\n\n

SQL Server LEN() function overview #<\/a><\/h2>\n\n\n\n

The LEN()<\/code> function returns the number of characters of an input string, excluding the trailing blanks.<\/p>\n\n\n\n

Here’s the syntax of the LEN()<\/code> function:<\/p>\n\n\n

LEN(string_expression)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the string_expression<\/code> can be a literal character string, string expression, or a character or binary column.<\/p>\n\n\n\n
SQL Server LEN() function examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples of using the LEN()<\/code> function.<\/p>\n\n\n\n
1) Using LEN() function with a literal string #<\/a><\/h3>\n\n\n\n
The following example uses the LEN function to return the number of characters of the string SQL Server LEN<\/code> and the same string with trailing blanks.<\/p>\n\n\n
SELECT<\/span> \n  LEN<\/span>('SQL Server LEN'<\/span>) length<\/span>, \n  LEN<\/span>('SQL Server LEN   '<\/span>) length_with_trailing_blanks;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
length      length_with_trailing_blanks\r\n----------- ---------------------------\r\n14          14<\/code><\/span><\/pre>\n\n\n
The output indicates that the LEN()<\/code> function\u00a0does not count the trailing blanks.<\/p>\n\n\n\n
If you want to consider the trailing spaces, you can use the DATALENGTH()<\/code> function. For example:<\/p>\n\n\n
SELECT \n  DATALENGTH('SQL Server LEN'<\/span>) data_length, \n  DATALENGTH('SQL Server LEN   '<\/span>) data_length_with_trailing_blanks;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
data_length data_length_with_trailing_blanks\r\n----------- --------------------------------\r\n14          17<\/code><\/span><\/pre>\n\n\n
2) Using the LEN() function with a data #<\/a><\/h3>\n\n\n\n
We’ll use the production.products<\/code> table from the sample database<\/a>.<\/p>\n\n\n\n
\"products\"<\/figure>\n\n\n\n
The following query retrieves the product names and their length using the LEN()<\/code> function:<\/p>\n\n\n
SELECT<\/span> \n  product_name, \n  LEN<\/span>(product_name) product_name_length \nFROM<\/span> \n  production.products \nORDER<\/span> BY<\/span> \n  product_name_length DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the partial output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
3) Using the LEN() function with NULL #<\/a><\/h3>\n\n\n\n
The following example uses the LEN() function with NULL, which results in NULL:<\/p>\n\n\n
SELECT \n  LEN(NULL<\/span>) result;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
result\r\n-----------\r\nNULL<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the LEN()<\/code> function to return the number of characters of a string, excluding trailing spaces.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
The LEN()<\/code> function returns a value whose data type is BIGINT<\/a><\/code> if string_expression<\/code> is of the VARCHAR(max)<\/a><\/code>, NVARCHAR(max)<\/a><\/code> or VARBINARY(max)<\/code> data type; or INT<\/a><\/code> otherwise.<\/p>\n\n\n\n