|
Chris Maunder wrote: Hands up who has ever noticed the "excluding trailing blanks" bit?
Me.
It amazed me the first time I discovered it as well. I only figured it out after trying to figure out why some code was failing to behave as it should.
I added documentation to the stored procedure to clearly call it out, so the next developer (perhaps even me) would not be surprised either.
I believe, but I could be mistaken, it only applies to a the data type 'char'. Your constant is a 'char'. It behaves as expected with 'varchar'.
|
|
|
|
|
I wonder if this has anything to do with CHAR is fixed length and VARCHAR is variable length?
modified 27-Sep-17 15:53pm.
|
|
|
|
|
Slacker007 wrote: I wonder if this has anything to do with CHAR is fixed length and VARCHAR is variable length?
Yup. I know this is a question about SQL Server, but I have a feeling that they have adopted some old logic from Oracle.
In Oracle the CHAR type will automatically pad data with spaces so the data field always occupies the max length you specified.
CHAR versus VARCHAR2 Semantics[^]
In that context, the behavior of len() is actually the only one that makes sense  .
So, to keep your sanity you never want to use CHAR but always VARCHAR/VARCHAR2  . Or, in Postgres - just use TEXT to get rid of all those pesky length constraints as well.
|
|
|
|
|
Jan Holst Jensen2 wrote: Yup. I know this is a question about SQL Server, but I have a feeling that they have adopted some old logic from Oracle.
Could be. The timeline makes it possible. Sybase (precursor to SQL Server) was founded after Oracle.
|
|
|
|
|
If this was QA, I'd be fighting the urge - a strong urge - to reply "RTFM".
But it isn't, so I don't have to... 
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
This is the point: I've read the manual a million times.
And I never noticed the proviso.
What kills me, I think (apart from noisy bagpipes and people who don't indicate when turning) is that they've taken a method (LEN) that is used everywhere for the same thing - get the length of something - and bent it slightly. The length of something except for a bit at the end, sometimes.
I propose that we, immediately, rename DATALENGTH to LEN, and LEN to LENWITHOUTTHETRAILINGSPACES, and to hell with the destruction this will cause to civilisation.
cheers
Chris Maunder
|
|
|
|
|
Or, I dunno, embrace object databases as a profession.
Just saying.
"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli
|
|
|
|
|
Holy crap, I propose T-SQL gets killed and replaced with ANSI SQL...
I've been working with enterprise databases for almost 15 years now (plus a few more in general programming) and never have I ever read a manual for a string length function!
You just google "<language> string lenght" and notice the snippet of the first post "select len(mycol) from.." and there you go, you know it.
I would NEVER expect a len() to function as len(rtrim())... that's just insane; any other programming languages do this sort of (sorry, but it is...) idiotic thing?
The more I learn about TSQL, the more I enjoy the fact the most of the bigger companies use Oracle DB... 
Maybe next time I get a client with MS (so rare, w00t), I'm really gonna have to RT(whole)FM, even for very standard functions like len()!!?!? Insane, I tell you!
Maybe I'll find that substr() doesn't consider ponctuation or something...??
|
|
|
|
|
Exactly!
cheers
Chris Maunder
|
|
|
|
|
SELECT DATALENGTH(' ');
> 5
|
|
|
|
|
Sooo... there are probably other things for which you have not yet RTFMed?
modified 29-Sep-17 16:57pm.
|
|
|
|
|
Does this scare you? 
cheers
Chris Maunder
|
|
|
|
|
Me! Just recently I had the same issue. My workaround was to use a replace function and then get the length of that string.
AND LEN(REPLACE(FieldWithValue, ' ', '*')) = 11
Later on I used
Datalength
|
|
|
|
|
|
For those who reply RTFM... I believe it would be far more cost effective to just allow the ensuing bug to be reported by the end user and fix it then. 
|
|
|
|
|
In a DB that blank pads all columns to their fixed size, this really makes sense.
LEN (Transact-SQL) | Microsoft Docs
snip
Remarks
LEN excludes trailing blanks. If that is a problem, consider using the DATALENGTH (Transact-SQL) function which does not trim the string. If processing a unicode string, DATALENGTH will return twice the number of characters. The following example demonstrates LEN and DATALENGTH with a trailing space.
|
|
|
|
|
Yes - I understand the function and how it works. My point was that I'd missed that tiny little proviso. For years. It shocked me.
Storing padding data inside a database? Please no. That's awful.
cheers
Chris Maunder
|
|
|
|
|
For a short field size, it is more efficient to just pad it out. Say a 4 byte field that is only storing 3 bytes of a string.
|
|
|
|
|
I did. I missed your "hands up" part though the first time around.
|
|
|
|
|
It's a fudge, implemented because of the 'char' fixed-length datatype:
declare @a char(10)
set @a=''
print len(@a) -- =0. But it doesn't. In reality, @a is ten spaces...
set @a='a'
print @a+'b' -- prints "a b"
print len(@a) -- =1
So there it is... I hate it too.
|
|
|
|
|
Alas, I just got smacked by this today even though I know not to trust LEN.
In this case, though I was using VARBINARY(16) to hold binary IP addresses -- and testing the length to determine IPv4 vs IPv6.
And today someone reported that it fails for IP addresses where the last byte is 32. 
|
|
|
|
|
Is a programmer one who prefers the metric system?
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Your remark has neither meter nor rhyme.
If you have an important point to make, don't try to be subtle or clever. Use a pile driver. Hit the point once. Then come back and hit it again. Then hit it a third time - a tremendous whack.
--Winston Churchill
|
|
|
|
|
atto boy! Have yourself some pico de gallo!
"the debugger doesn't tell me anything because this code compiles just fine" - random QA comment
"Facebook is where you tell lies to your friends. Twitter is where you tell the truth to strangers." - chriselst
"I don't drink any more... then again, I don't drink any less." - Mike Mullikins uncle
|
|
|
|
|
At last the metric system is conquering Britain, inch by inch.
... such stuff as dreams are made on
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
