|
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
|
|
|
|
|
Is that irony I smell?
I am not the one who knocks. I never knock.
In fact, I hate knocking.
|
|
|
|
|
No. Sorry, that was me.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
I am not the one who knocks. I never knock.
In fact, I hate knocking.
|
|
|
|
|
Sorry, but the quality of humor in this thread stinks!
/ravi
|
|
|
|
|
Well that's just your 2 scents.
"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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
