|
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
|
|
|
|
|
Now I'm really fuming. Put that in your pipe and smoke it!
/ravi
|
|
|
|
|
Don't odor me around!
"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
|
|
|
|
|
Good heavens, I would never stink of doing that!
/ravi
|
|
|
|
|
I'm breathing a sigh of relief, I thought I was gonna have to reek some havoc.
"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
|
|
|
|