Add setting so LEN counts trailing whitespace
Maybe there is a historic reason why LEN('a') and LEN('a ') return the same number.
But that's stupid.
The internet is awash with people like me who were stunned to discover this counter intuitive quirk (Google it).
None of the solutions to this problem are particularly elegant or efficient:
I'm assuming since this has been baked in for ages, we can't change default behavior.
So how about:
- Add a parameter to the LEN function to count white space
- Add an SQL Option that more generally keeps SQL Server's hands off my white space - everywhere
van Kooten commented
Not even DATALENGTH can save you: https://stackoverflow.com/a/8250586/2084592
ΣΟΡΟΚΟΣ ΙΩΑΝΝΗΣ commented
I agree, please offer a way to count white space
This needs more support. What a silly problem to have. At least it would be nice for someone to explain the possible historic significance of the current behavior.
Microsoft's own documentation suggests to use DATALENGTH(expr) as an alternative. But this function returns the size in BYTES of the expression. NOT useful at all and potentially dangerous as you need to know the datatype you're working with. Please just add a parameter to LEN to include trailing white spaces!!! Something like LEN2(expr)???
Benjamin Levenson commented
Or an override, AKA LEN('abc ',1)=3 LEN('abc ',2)=4 with a default of 1 that allows the current code to keep working, but give additional options.
Stanley Sufficool commented
SELECT DATALENGTH('a ') = 2
Maybe just a new LENW (or whatever) function that includes whitespace. Code has to be touched up either way.