|
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
|
|
|
|
|
Are you secretly trying to ask a programming question in the Lounge? 
|
|
|
|
|
The only question I'd be asking is "am I blind and who let me near a keyboard?"
cheers
Chris Maunder
|
|
|
|
|
Chris Maunder wrote: Hands up who has ever noticed the "excluding trailing blanks" bit?
Slowly raises hand, expecting to be hit with a deluge of SQL questions.  
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
print len(' ')
Those look like leading blanks to me, not trailing blanks.
|
|
|
|
|
Exactly!
cheers
Chris Maunder
|
|
|
|
|
So it should return 1?
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
And you will only make that mistake twice: For the first and the last time. 
I am endeavoring, ma'am, to construct a mnemonic memory circuit using stone knives and bearskins.
|
|
|
|
|
Human being is the only animal that stumble twice with the same stone
M.D.V. 
If something has a solution... Why do we have to worry about?. If it has no solution... For what reason do we have to worry about?
Help me to understand what I'm saying, and I'll explain it better to you
Rating helpful answers is nice, but saying thanks can be even nicer.
|
|
|
|
|
I see 2 trailing blanks and 2 leading blanks surrounding a single blank.
|
|
|
|
|
This discussion is turning rather spacey.
|
|
|
|
|
Now you are shooting blanks
modified 19-Nov-18 21:01pm.
|
|
|
|
|
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;
|
|
|
|
|
Dear lord! 
|
|
|
|
|
One of the hazards of being the UI guy, and all of your UI's are localized - you can make UNICODE jokes  .
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.
|
|
|
|
|
2, he said. Snicker, chortle, guffaw. 
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;
|
|
|
|
|
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!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
