SQL does my head in
-
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
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'.
-
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?
-
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
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... :laugh:
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!
-
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... :laugh:
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!
but you already did. :laugh: so now you don't have to. Even more :laugh: :laugh:
-
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
SELECT DATALENGTH(' ');
> 5
-
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... :laugh:
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
-
Exactly!
cheers Chris Maunder
So it should return 1? :laugh:
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.
-
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
Sooo... there are probably other things for which you have not yet RTFMed?
-
Sooo... there are probably other things for which you have not yet RTFMed?
Does this scare you? ;)
cheers Chris Maunder
-
I see 2 trailing blanks and 2 leading blanks surrounding a single blank.
Nish Nishant Consultant Software Architect Ganymede Software Solutions LLC www.ganymedesoftwaresolutions.com
-
I see 2 trailing blanks and 2 leading blanks surrounding a single blank.
Nish Nishant Consultant Software Architect Ganymede Software Solutions LLC www.ganymedesoftwaresolutions.com
This discussion is turning rather spacey.
-
This discussion is turning rather spacey.
-
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
-
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.
Nish Nishant Consultant Software Architect Ganymede Software Solutions LLC www.ganymedesoftwaresolutions.com
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; -
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! :eek:
Nish Nishant Consultant Software Architect Ganymede Software Solutions LLC www.ganymedesoftwaresolutions.com
-
Dear lord! :eek:
Nish Nishant Consultant Software Architect Ganymede Software Solutions LLC www.ganymedesoftwaresolutions.com
One of the hazards of being the UI guy, and all of your UI's are localized - you can make UNICODE jokes :sigh:.
Software Zen:
delete this; -
One of the hazards of being the UI guy, and all of your UI's are localized - you can make UNICODE jokes :sigh:.
Software Zen:
delete this;:laugh:
Nish Nishant Consultant Software Architect Ganymede Software Solutions LLC www.ganymedesoftwaresolutions.com
