One of the things you want to be aware of when writing T-SQL is using the proper function for a particular problem. Someone posted a question asking about why they were getting a 0 for this code:

SELECT Mychar
    , '''' + mychar + ''''
   FROM dbo.MyTable

That gave me these results

I used the quotes in order to show that one of my columns has spaces trailing in one of the columns. I noticed that the poster was wondering why they had these results?

SELECT Mychar
    , LEN(mychar)
   FROM dbo.MyTable

In the table, clearly there are 4 characters for the row with “4D” and 5 characters for the next row. However the length is being returned as 0. If you were planning on testing for blank strings, or using some substring function, this could be an issue.

The reason is simple. LEN, as noted in Books Online, ignores trailing spaces. The description of the function is: Returns the number of characters of the specified string expression, excluding trailing blanks.

So if you have a space at the end of your string, or just a string of spaces, you don’t get the correct length. What should you use?

Datalength – This function is designed to show the number of bytes used by the string, not the characters. Code shown below:

SELECT
   MyID
 , '''' + mychar + ''''
 , LEN(mychar)
 , DATALENGTH(mychar)
   FROM dbo.MyTable

A good thing to be aware of if you are writing string test routines. LEN is the function I know most people use, but it is somewhat flawed, IMHO, in T-SQL

Filed under: Blog Tagged: syndicated, T-SQL