0

Could someone please explain me why does following query output '1'?

IF 'SQL ' LIKE 'SQL '       
    SELECT '1'
ELSE                        
    SELECT '0'

Output:

    1

Based on the clarification made in the link, I came to know, when we use LIKE with VARCHAR datatypes, it treats trailing whitespace as significant in the pattern (RHS) but not the match expression (LHS).

So, based on the above logic, in the above query, match expression 'SQL ' (LHS) is to be treated as 'SQL' and then to be matched with pattern 'SQL ' (RHS) and should be resulting 0. But we are getting '1' instead of '0'.

But for the below cases, we are getting expected results.

IF 'SQL' LIKE 'SQL '        
    SELECT '1'
ELSE                        
    SELECT '0'
    
IF 'SQL ' LIKE 'SQL'        
    SELECT '1'
ELSE                        
    SELECT '0'

Output:

    0
    1
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
abhishek
  • 49
  • 9
  • I think you've got muddled with your example: the "unexpected" case at the top of the question is exactly the same as the second "expected" case at the end of it. – IMSoP Jul 17 '22 at 11:33
  • 2
    The left side is trimmed as the specification says, in normal cases the right space will be trimmed when you insert it. – nbk Jul 17 '22 at 11:51
  • 1
    You could take some uncertainty out of the experiment by explicitly defining datatypes. What you have there is not really a practical case. – Nick.Mc Jul 17 '22 at 12:00

1 Answers1

1

"Insignificant" is not the same as "removed"; it means something more like "not required". A different way of stating the rule would be this:

If the pattern includes a trailing space, then that space is required to exist in the string being matched (the space is a "significant" part of the pattern); but if the string being matched includes a trailing space, that space is not required to exist in the pattern (the space is not a "significant" part of the string).

Put this way, the example makes perfect sense - the pattern has a "significant" space, so that space is required in the string being matched; the string does indeed have that space, so the match succeeds.

On the other hand, when the string on the left hand side has an "insignificant" space, it is not required for that space to also appear in the pattern; so patterns with or without that space will match the string.

IMSoP
  • 89,526
  • 13
  • 117
  • 169
  • **_if no. of Trailing spaces in Expression (LHS) >= no. of Trailing spaces in Pattern (RHS), Then Match occurs_**, _Assuming others characters are same._ After analyzing all of yours comments and provided link, Above is the my takeaway. Thank You all. – abhishek Jul 17 '22 at 13:22
  • 1
    @abhishek Yes, I think that's a reasonable way of putting it. Glad I could help. – IMSoP Jul 17 '22 at 14:05