Difference between MySQL (SQL) SUBSTR - ORDER BY statements-CodePudding

Please help me understand the difference between the following 2 SQL statements, where the difference is in the way ORDER BY using SUBSTR is performed:

Query 1:

SELECT * FROM countrylanguage 
ORDER BY SUBSTR(Language,-3,3)
LIMIT 10;

Query 2:

SELECT * FROM countrylanguage
ORDER BY Language LIKE '%___'
LIMIT 10;

CodePudding user response：

ORDER BY substr(language, -3, 3) order the results lexicographically by the last three characters of the values of the column language, if the values is at least three characters long. For all rows where the value is shorter than three characters, substr(language, -3, 3) will yield the empty string, so these rows go to the top.

As LIKE '%___' matches all strings with at least three characters -- the '%' wildcard means any character an arbitrary number of times (also zero times) and '_' any character exactly one time -- ORDER BY language LIKE '%___' puts the rows where the value of language is less than three characters long at the top, the ones where the values is at least three characters long at the bottom.

Rows where language is NULL are put atop in both cases.

CodePudding user response：

When you use SUBSTR after ORDER BY you order result of your query by some part, in this case STRING. If you query SUBSTR(Language,-3,3) th result is ordered by letters of Language values not by first letter as usual but by last 3 letters.

Normal result: ARABIC, FRENCH, GERMAN

Result of your query ARABIC, GERMAN, FRENCH

When you use %Like the matching is fuzzy, for exmaple if you query LIKE "%ENGLISH", result is: AMERICAN ENGLISH, BRITISH ENGLlISH, SOUTH AFRICAN ENGLISH