SQL does my head in
-
I wonder if this has anything to do with CHAR is fixed length and VARCHAR is variable length?
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 :wtf: . 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.
-
LEN (Transact-SQL) Returns the number of characters of the specified string expression, excluding trailing blanks So
> print len('hello')
5
and
> print len(' ')
0
Hands up who has ever noticed the "excluding trailing blanks" bit? Up until an hour ago, not me. Which boggles my mind.
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
-
LEN (Transact-SQL) Returns the number of characters of the specified string expression, excluding trailing blanks So
> print len('hello')
5
and
> print len(' ')
0
Hands up who has ever noticed the "excluding trailing blanks" bit? Up until an hour ago, not me. Which boggles my mind.
cheers Chris Maunder
Pffffst! Human!
-
LEN (Transact-SQL) Returns the number of characters of the specified string expression, excluding trailing blanks So
> print len('hello')
5
and
> print len(' ')
0
Hands up who has ever noticed the "excluding trailing blanks" bit? Up until an hour ago, not me. Which boggles my mind.
cheers Chris Maunder
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. ;P
-
Hmm. Actually, there could be an infinite number of zero width spaces[^] in that string in addition to the 'normal' spaces, if you're using UNICODE.
Software Zen:
delete this;You can't do that without IBM's new Infinite Storage. But they're in short supply. Their first customer ordered two.
-
You can't do that without IBM's new Infinite Storage. But they're in short supply. Their first customer ordered two.
2, he said. Snicker, chortle, guffaw. :-D
Software Zen:
delete this; -
LEN (Transact-SQL) Returns the number of characters of the specified string expression, excluding trailing blanks So
> print len('hello')
5
and
> print len(' ')
0
Hands up who has ever noticed the "excluding trailing blanks" bit? Up until an hour ago, not me. Which boggles my mind.
cheers Chris Maunder
In a DB that blank pads all columns to their fixed size, this really makes sense. [LEN (Transact-SQL) | Microsoft Docs](https://docs.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql) 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. -
LEN (Transact-SQL) Returns the number of characters of the specified string expression, excluding trailing blanks So
> print len('hello')
5
and
> print len(' ')
0
Hands up who has ever noticed the "excluding trailing blanks" bit? Up until an hour ago, not me. Which boggles my mind.
cheers Chris Maunder
-
In a DB that blank pads all columns to their fixed size, this really makes sense. [LEN (Transact-SQL) | Microsoft Docs](https://docs.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql) 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
-
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 " 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? :wtf: The more I learn about TSQL, the more I enjoy the fact the most of the bigger companies use Oracle DB... :-O 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
-
Hmm. Actually, there could be an infinite number of zero width spaces[^] in that string in addition to the 'normal' spaces, if you're using UNICODE.
Software Zen:
delete this; -
don't even get me started on that. I have been burned so many times on the zero length spaces. They are very spacey
To err is human to really mess up you need a computer
rnbergren wrote:
I have been burned so many times on the zero length spaces
I actually found a good use for them. I had a comma-separated value file I was dealing with, and I didn't want embedded line breaks. I replaced the line breaks with zero-width spaces, and voila! As I recall, even Excel handled them correctly.
Software Zen:
delete this; -
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 :wtf: . 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.
-
LEN (Transact-SQL) Returns the number of characters of the specified string expression, excluding trailing blanks So
> print len('hello')
5
and
> print len(' ')
0
Hands up who has ever noticed the "excluding trailing blanks" bit? Up until an hour ago, not me. Which boggles my mind.
cheers Chris Maunder
-
LEN (Transact-SQL) Returns the number of characters of the specified string expression, excluding trailing blanks So
> print len('hello')
5
and
> print len(' ')
0
Hands up who has ever noticed the "excluding trailing blanks" bit? Up until an hour ago, not me. Which boggles my mind.
cheers Chris Maunder
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. :sigh: -
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