March 19, 2008 at 3:01 pm
When executing the following SQL
SELECT ASCII(''), ASCII(' '),
CASE WHEN ''=SPACE(1) THEN 'TRUE' ELSE 'FALSE ' END ,
CASE WHEN NULL=' ' THEN 'true' ELSE 'false' END
I get strange results:
--------------------------------
NULL 32 TRUE false.
I suspect that explanation lies somewhere within settings, but I don't have permissions to look at those... Can anybody at least give me direction on where to look for an explanation?
(This issue came to light when I started to run data checks and to me, empty string= space doesn't sounds right)
Thank you.
March 19, 2008 at 3:12 pm
Makes sense to me. What part seems wrong to you?
I can tell you that [font="Courier New"]( '' = ' ' )[/font] in SQL because they are converted to Varchar's and varchars don't count trailing spaces for comparisons.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 19, 2008 at 3:18 pm
Shamanka (3/19/2008)
When executing the following SQLSELECT ASCII(''), ASCII(' '),
CASE WHEN ''=SPACE(1) THEN 'TRUE' ELSE 'FALSE ' END ,
CASE WHEN NULL=' ' THEN 'true' ELSE 'false' END
I get strange results:
--------------------------------
NULL 32 TRUE false.
As for the other three:
[font="Courier New"]ASCII('') is NULL[/font] because the [font="Courier New"]ASCII [/font]function returns the Ascii code of the first character. There are no characters, so nothing to return, hence [font="Courier New"]NULL[/font].
[font="Courier New"]ASCII(' ')[/font] is 32 because that's the Ascii code for a space.
And [font="Courier New"]NULL [/font]does not equal [font="Courier New"]' '[/font], because [font="Courier New"]NULL [/font]is never equal to something else, even another [font="Courier New"]NULL[/font].
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2008 at 3:52 pm
Perfectly said RBarryYoung 🙂
March 20, 2008 at 4:06 pm
Thanks, Adam.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply