Select Statement using Mysql

Question

I am new to using databases,

I have a table ( easy_drinks )

+------------+-------------+---------+
| Drink_name |    main     | amount1 |
+------------+-------------+---------+
| Blackthorn | Blackthorn  | 1.5     |
| BlueMoon   | soda        | 1.5     |
| OhMyGosh   | peachnectar | 1       |
+------------+-------------+---------+

I have a Query

SELECT Drink_name 
FROM easy_drinks 
WHERE main > 'soda'
;

It is giving results as Blakcthorn

Can you please explain how string comparison occurs between i.e with main and 'soda' ?

Answer 1

It compairs the strings using the underlying values of the collation the fields use in a lexiographic order. That means that capitals are "bigger" (before) non capitals.

Note: If both strings use a different collation, one will be converted.

If you do not prefer to distinguish capitals and non capitals use something like

SELECT Drink_name 
FROM easy_drinks 
WHERE LOWER(main) > 'soda'
;

You can also use the strcmp function (see here ).

SELECT Drink_name 
FROM easy_drinks 
WHERE strcmp(LOWER(main),'soda') = 1
;