SQL Server T-SQL User-Defined Function of the Week

Getting the Full Length of a String Including Trailing Spaces

Volume 1 Number 16    March 4, 2003

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

The SQL Server Worldwide Users Group runs a web BLOG with entries
about SQL Server.  I saw one the other day that had a reply that
got me thinking about this week's UDF.

You can see the blog and reply at:
http://www.sswug.org/blogview/default.asp?replies=40
The original issue was getting the length of a character string
including any trailing spaces.  The built-in LEN function 
ignores trailing spaces when it reports the length. But sometimes
you want to know the whole length of the string.

This week's UDF, udf_Txt_FullLen, returns it for us. Here's the 
CREATE FUNCTION script:

/------------- Copy From Below this line ----------------------\
CREATE FUNCTION dbo.udf_Txt_FullLen (

    @Input sql_variant -- Input we want the length of. Takes any
                       -- short text type, such as char, varchar
                       -- nvarchar, nchar.  Not text or ntext.
) RETURNS int-- Length of input including trailing spaces.
/* 
* Returns the length of a character string including trailing 
* blanks. The built-in LEN function excludes trailing blanks 
* when it calculates the length.  This function uses a 
* sql_variant so that both UNICODE and ANSI strings can be @input
*
* Common Usage:
SELECT LEN('ABC ') as [Built-in LEN]
     , dbo.udf_Txt_FullLen ('ABC ') [From udf_Txt_FullLen]
*
* TEST:
PRINT 'Test 1 ' + CASE WHEN dbo.udf_Txt_FullLen(N'ABCD  ') = 6
                       THEN 'Worked' else 'ERROR' END
* © Copyright 2002 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically. -- Originally Vol 1 #16:
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/

AS BEGIN

    DECLARE @BaseType sysname  -- the underlying type
          , @ActualLen int     -- the length of the data.

    SELECT @BaseTYPE = CAST(SQL_VARIANT_PROPERTY(@Input
                                              , 'BaseType') 
                             as sysname)
         , @ActualLen = DATALENGTH(@Input)

    RETURN CASE WHEN @BaseTYPE IN ('char', 'varchar')
                     THEN @ActualLen
                WHEN @BaseType IN ('nchar', 'nvarchar')
                     THEN @ActualLen / 2
                ELSE
                      @ActualLen
                END 
END	
GO

GRANT SELECT ON [dbo].[udf_Txt_FullLen] to PUBLIC
GO
\------------ Stop copying above this line --------------------/

There are a few different ways that this function can be written
I chose to use a sql_variant for the @Input parameter and 
the built-in DATALENGTH function to calculate the length of the
string.  Those choices allow udf_Txt_FullLen to work on both
single-byte (char, varchar) and double-byte (nvarchar, nchar)
strings.

Since I don't know of any convenient data with spaces at the 
end of strings, the sample script that follows creates an
table in tempdb and populates it with a few appropriate strings.
Here it is:

/------------- Copy From Below this line ----------------------\
CREATE TABLE #ExampleStrings  (String varchar(30))

INSERT INTO #ExampleStrings VALUES ('No characters at end')
INSERT INTO #ExampleStrings VALUES ('1 xtra chars at end ')
INSERT INTO #ExampleStrings VALUES ('2 xtra chars at end  ')
INSERT INTO #ExampleStrings VALUES ('3 xtra chars at end   ')

SELECT String + '<' as String
     , LEN(String) [Length according to LEN]
     , dbo.udf_Txt_FullLen(String) as [Full Length]
   FROM #ExampleStrings

DROP TABLE #ExampleStrings
GO
\------------ Stop copying above this line --------------------/
(Results)
String                          LEN         udf_Txt_FullLen 
------------------------------- ----------- --------------- 
No characters at end<                    20              20 
1 xtra chars at end <                    19              20 
2 xtra chars at end  <                   19              21 
3 xtra chars at end   <                  19              22 
(End of results)

Feel free to share this newsletter and the UDF it contains with
anyone interested in SQL Server.  Please leave the UDF and the
newsletter intact when you share it.  Thanks.

+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick