SQL "like" statement: 'Jeff' LIKE 'Jeff' = false?

EDIT: Problem solved. It was my reading that was incorrect, not the SQL :p

Hi!开发者_JAVA技巧

I was just reading in my database book about the LIKE SQL statement. It said this:

SELECT whatever FROM whereever WHERE 'Jeff' LIKE 'Jeff';

It continued to say that the statement 'Jeff' LIKE 'Jeff' would always return false. The book did not tell me why, nor can I find this anywhere else. Does this then mean that the following SQL also would return null?

SELECT W.name FROM whereever W WHERE w.name LIKE 'Jeff';

For reference, the book is: Database Management Systems: Ramakrishnan - Gehrke From McGRAW - Hill. International edition. ISBN 0-07-123151-X PAGE 140.

I took a look at that page (Amazon "search inside this book") and the key thing that you're missing is that the author is making a point there about whitespace. The book actually says

Thus, 'Jeff'='Jeff ' is true, while 'Jeff' LIKE 'Jeff ' is false.

Note the spaces!

Just to make it clear why the mistake occurred, here's the text:

Since the space is at the end of the line, it's hard to see. But as I say below, the quote mark wouldn't wrap to the next line if there were no space there.

MySQL would disagree with your book (not sure about other DBMSes):

mysql> SELECT 'Jeff' LIKE 'Jeff';
+--------------------+
| 'Jeff' LIKE 'Jeff' |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

Not sure why it would say that - 'Jeff' like 'Jeff' is exactly true. In which case, it will return ALL the rows from 'whereever'

In your second statement, it will only return rows where the the 'name' column is exactly 'Jeff'. If it is 'Jeff something', it won't return. If you want to return something like that, then you have to do something like 'w.name like 'Jeff%'. There are many more variations of this, which I'm sure you'll discover as you read on more...

It could depend on the DBMS implementation, but if you run "select 'jeff' like 'jeff';" from MySQL it return "1" (aka true).

Totally false. The following statement returns 'Y' (because the condition evaluates to true:

select 'Y' where 'Jeff' LIKE 'Jeff'

... while this statement returns no rows (because it evaluates to false):

select 'Y' where 'Jeff' LIKE 'John'

I don't know what these authors were thinking, but LIKE comparisons without any wildcards are perfectly acceptable; the left and right sides are compared semantically for exact equality.

That section of the book deals with blanks. They are saying that, in sql,

'Jeff' = 'Jeff ' (note the extra space) results in TRUE but

'Jeff' LIKE 'Jeff ' (again, with the extra space) results in FALSE

Hope that clears things up.