Sql-docs: Page contains an incorrect statement.

[PicayuneOne]

In the section with a lavender background, this sentence is incorrect.: "Use the LEN to return the number of characters encoded into a given string expression" because the Len function does NOT include trailing spaces. It's been more than 15 years of people complaining about that quirk and yet Microdaft STILL does not provide an alternate function to return the true length of a string.
There's many reasons why multiple and trailing spaces should not be ignored, but Microsoft just doesn't get it.
The programmer put multiple and trailing spaces there FOR A REASON.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 2ff0d040-6a32-aa7f-0b6e-91a392a1e4e0
• Version Independent ID: 5560f1d4-948f-e5de-738b-5950000505a1
• Content: LEN (Transact-SQL) - SQL Server
• Content Source: docs/t-sql/functions/len-transact-sql.md
• Product: sql
• Technology: t-sql
• GitHub Login: @pmasl
• Microsoft Alias: mikeray

[LarnuUK]

The point you're making is covered in the Remarks section, and the Document gives an alternative as well:

LEN excludes trailing spaces. If that is a problem, consider using the DATALENGTH (Transact-SQL) function which does not trim the string.

If you do use DATALENGTH just remember that an nvarchar (or nchar) will use 2 bytes per character, not 1. If you don't know what datatype the column is, that's a totally different (design) problem, in my view.

I don't see anything wrong with what the Documentation states, as it's tell you exactly how the function works. Changing the documentation to say otherwise would be wrong.

[PicayuneOne]

And yet the statement I complained about IS INCORRECT. You cannot say things that are incorrect then qualify your statement in ANOTHER PARAGARPH.

And precisely how do I know the data type of my variable when it may be the result of several text manipulating functions and the + operator? So using DataLength is an impractical solution. So there is no alternate version of the Len function. Which is what I said in my original complaint.

Microsoft is constantly turning a blind eye to their users. There’s hundreds of search engine hits that discuss the confusion caused by this one little quirk and Microsoft refuses to rectify it. The pain it causes is been extreme.
From: Thom A [mailto:[email protected]]
Sent: Saturday, October 19, 2019 1:38 PM
To: MicrosoftDocs/sql-docs [e-mail]
Cc: PicayuneOne [e-mail]; Author [e-mail]
Subject: Re: [MicrosoftDocs/sql-docs] Page contains an incorrect statement. (#3352)

The point you're making is covered in the Remarks section, and the Document gives an alternative as well:
LEN excludes trailing spaces. If that is a problem, consider using the DATALENGTH (Transact-SQL) function which does not trim the string.
If you do you DATALENGTH just remember that an nvarchar (or nchar) will use 2 bytes per character, not 1. If you don't know what datatype the column is, that's a totally different (design) problem, in my view.
I don't see anything wrong with what the Documentation states, as it's tell you exactly how the function works. Changing the documentation to say otherwise would be wrong.
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub https://github.com/MicrosoftDocs/sql-docs/issues/3352?email_source=notifications&email_token=AMPH5S6VGGSR3YHZ63GAIETQPNV2DA5CNFSM4JCKNHKKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEBX4HFQ#issuecomment-544195478 , or unsubscribe https://github.com/notifications/unsubscribe-auth/AMPH5S2Q4WVQJFYXN2J32VLQPNV2DANCNFSM4JCKNHKA .

[LarnuUK]

And yet the statement I complained about IS INCORRECT. You cannot say things that are incorrect then qualify your statement in ANOTHER PARAGARPH.

So what exactly, in your opinion, is incorrect here in the document? The document states that LEN returns the number of characters in a string, and also states that it does not include trailing spaces. That is exactly how LEN works. What is incorrect about those statements? What would you suggest the wording of the page should be instead if that isn't the behaviour of the function?

And precisely how do I know the data type of my variable when it may be the result of several text manipulating functions and the + operator?

I'm not sure of your point here, I'm afraid. As the developer you should know the datatypes you are working with. SQL Server uses something called Data type precedence to determine the data type of an expression. If you are concatenating a lot of "string" (varchar and nvarchar) data types then the final value will always be the data type with the highest precedence. nvarchar has a higher precedence than varchar, so even if you have 1 nvarchar value, and concatenate 100 varchar values to it, the end result will be an nvarchar. Considering you know the data types of your columns, it therefore makes it trivial to know the return type: "Are any of the string an nvarchar?" If yes, then the return type is nvarchar, if no it's varchar.

If it's a variable, you would have had to DECLARE them, so you review your SQL to check the type you declared (but you should also always be using a varchar to compare to a varchar, as not doing so can have severe performance impacts).

So there is no alternate version of the Len function. Which is what I said in my original complaint.

Are you, perhaps, therefore suggesting that the behaviour of LEN should be changed? That would be a huge breaking change to SQL Server and I would hope Microsoft never implement that change. If you're asking for a new function, then the Docs aren't the right place for it, and you should make a request on the SQL Server Forums. If you're looking for a user function that counts length, including spaces, you could always create your own. Perhaps something like:

CREATE FUNCTION dbo.LEN_SPACES (@String nvarchar(MAX)) --varchar will be casted upwards
RETURNS int
AS BEGIN

    DECLARE @i int;
    SET @i = DATALENGTH(@String) / 2; --As nvarchar, will always be divided by 2
    RETURN @i;
END;

If you're using 2019, I suggest making use of the inlining functionality of Scalar functions, instead of using a multi-line function.

[msebolt]

@PicayuneOne @LarnuUK Thank you for the feedback.

I think the wording in the note is incorrect even though its context, which is to highlight the differences between LEN and DATALENGTH is valid and have reworded the document accordingly.

The changes are under review.

[pmasl]

@PicayuneOne while you make some fair points, I suggest you open a design change request for a LEN-like function that doesn't ignore trailing spaces at https://aka.ms/sqlfeedback.
And while I understand your scenario is of the utmost importance, the engineering team doesn't take lightly to changing a behavior because that may break millions of workloads depending on a specific behavior. If a behavior is changed, it typically has to do with improving precision or addressing potential wrong results, none of which are at play here.
I will be reviewing possible changes, but unlike you state, the current form is not incorrect, it's at most incomplete, if you choose to willingly disregard the remark in the same article.
Signing-off

[LarnuUK]

And while I understand your scenario is of the utmost importance, the engineering team doesn't take lightly to changing a behavior because that may break millions of workloads depending on a specific behavior. If a behavior is changed, it typically has to do with improving precision or addressing potential wrong results, none of which are at play here.

Why I feel that if the OP wanted to have a built in function behaviour to count trailing spaces, they would be better asking for a new function to address the requirement. I completely agree that changing the behaviour of LEN would be a huge breaking change. It would certainly, as well, have huge detrimental effect on those using the char and nchar data types.

A change to LEN to count trailing spaces would, I expect, be received incredibly poorly from the SQL Server Communities and users.