The problem with len()?

Published 03 October 09 10:31 PM | MartinBell
I read the blog post by Madhivanan and it reminded me of a documented behaviour of the LEN function which a lot of people tend to forget.

If you had the following script:

SET NOCOUNT ON
SET ANSI_PADDING ON
CREATE TABLE tab_a ( col1 char(15) NULL, col2 char(15) NOT NULL, col3 varchar(15) ) ;

INSERT INTO tab_a ( col1, col2, col3 ) VALUES ( 'ABC   ', 'ABC   ','ABC   ' );

SELECT '!' + col1+ '!' as [col1], LEN(col1)  AS [length_col1], DATALENGTH(col1)  AS [datalength_col1]
FROM tab_a
SELECT '!' + col2+ '!' as [col2], LEN(col2)  AS [length_col2], DATALENGTH(col2)  AS [datalength_col2]
FROM tab_a
SELECT '!' + col3+ '!' as [col3], LEN(col3)  AS [length_col3], DATALENGTH(col3)  AS [datalength_col3]
FROM tab_a

DROP TABLE tab_a
GO

You get the results:

col1              length_col1 datalength_col1
----------------- ----------- ---------------
!ABC            ! 3           15             

col2              length_col2 datalength_col2
----------------- ----------- ---------------
!ABC            ! 3           15             

col3              length_col3 datalength_col3
----------------- ----------- ---------------
!ABC   !          3           6


Which shows that the length function will remove any trailing blanks at the end of the strings. This could be a problem if you had trailing blanks and use MAX(LEN(col1)) to determine the size of a variable used to hold a concatenation. e.g.

SET NOCOUNT ON
SET ANSI_PADDING ON
DECLARE @a char(6)

CREATE TABLE tab_a ( col1 char(15) NULL, col2 char(15) NOT NULL, col3 varchar(15) ) ;

INSERT INTO tab_a ( col1, col2, col3 ) VALUES ( 'ABC   ', 'ABC   ','ABC   ' ); 

SET @a = (SELECT TOP 1 col1 + col2 FROM tab_a) ;

SELECT '!' + @a + '!'  AS [col1andcol2]

DROP TABLE tab_a
GO

The resulting value in @a will be:

col1andcol2
-----------
!ABC   !

Changing the ANSI_PADDING to OFF will cause null-able character columns to truncate trailing blanks so you get the same results from LEN and DATALENGTH for null-able character and for varchar columns

SET NOCOUNT ON
SET ANSI_PADDING OFF
CREATE TABLE tab_a ( col1 char(15) NULL, col2 char(15) NOT NULL, col3 varchar(15) ) ;

INSERT INTO tab_a ( col1, col2, col3 ) VALUES ( 'ABC   ', 'ABC   ','ABC   ' );

SELECT '!' + col1+ '!' as [col1], LEN(col1)  AS [length_col1], DATALENGTH(col1)  AS [datalength_col1]
FROM tab_a
SELECT '!' + col2+ '!' as [col2], LEN(col2)  AS [length_col2], DATALENGTH(col2)  AS [datalength_col2]
FROM tab_a
SELECT '!' + col3+ '!' as [col3], LEN(col3)  AS [length_col3], DATALENGTH(col3)  AS [datalength_col3]

FROM tab_a

DROP TABLE tab_a
GO


The results are:

col1              length_col1 datalength_col1
----------------- ----------- ---------------
!ABC!             3           3

col2              length_col2 datalength_col2
----------------- ----------- ---------------
!ABC            ! 3           15

col3              length_col3 datalength_col3
----------------- ----------- ---------------
!ABC!             3           3

 

The documentation says "trailing blanks" and not "trailing white space" so characters such as tabs are still counted i.e.

SET NOCOUNT ON
SET ANSI_PADDING ON
CREATE TABLE tab_a ( col1 char(15) NULL, col2 char(15) NOT NULL, col3 varchar(15) ) ;

INSERT INTO tab_a ( col1, col2, col3 ) VALUES ( 'ABC   '+CHAR(9), 'ABC   '+CHAR(9),'ABC   ' +CHAR(9));

SELECT '!' + col1+ '!' as [col1], LEN(col1)  AS [length_col1], DATALENGTH(col1)  AS [datalength_col1]
FROM tab_a
SELECT '!' + col2+ '!' as [col2], LEN(col2)  AS [length_col2], DATALENGTH(col2)  AS [datalength_col2]
FROM tab_a
SELECT '!' + col3+ '!' as [col3], LEN(col3)  AS [length_col3], DATALENGTH(col3)  AS [datalength_col3]
FROM tab_a

DROP TABLE tab_a
GO

The results are:

col1              length_col1 datalength_col1
----------------- ----------- ---------------
!ABC              ! 7           15             

col2              length_col2 datalength_col2
----------------- ----------- ---------------
!ABC              ! 7           15             

col3              length_col3 datalength_col3
----------------- ----------- ---------------
!ABC      !         7           7

 
And:

SET NOCOUNT ON
SET ANSI_PADDING OFF
CREATE TABLE tab_a ( col1 char(15) NULL, col2 char(15) NOT NULL, col3 varchar(15) ) ;

INSERT INTO tab_a ( col1, col2, col3 ) VALUES ( 'ABC   '+CHAR(9), 'ABC   '+CHAR(9),'ABC   ' +CHAR(9));

SELECT '!' + col1+ '!' as [col1], LEN(col1)  AS [length_col1], DATALENGTH(col1)  AS [datalength_col1]
FROM tab_a
SELECT '!' + col2+ '!' as [col2], LEN(col2)  AS [length_col2], DATALENGTH(col2)  AS [datalength_col2]
FROM tab_a
SELECT '!' + col3+ '!' as [col3], LEN(col3)  AS [length_col3], DATALENGTH(col3)  AS [datalength_col3]
FROM tab_a

DROP TABLE tab_a
GO

The results are:

col1              length_col1 datalength_col1
----------------- ----------- ---------------
!ABC      !         7           7

col2              length_col2 datalength_col2
----------------- ----------- ---------------
!ABC              ! 7           15              

col3              length_col3 datalength_col3
----------------- ----------- ---------------
!ABC      !         7           7

 
Filed under:

Comments

# Dew Drop – October 5, 2009 | Alvin Ashcraft's Morning Dew said on October 5, 2009 12:57 PM:

Pingback from  Dew Drop – October 5, 2009 | Alvin Ashcraft's Morning Dew

This Blog

SQL Blogs

Syndication