Search code examples
mysqlsql-order-bymysql-5.5

MySQL TRIM leading space in order by clause issue

Hi everyone this is simple thing to solve but could not find a way out. I want to apply ORDER BY on a name field which have leading space in some values because of that ORDER BY does not produce the desired results. So I apply TRIM in ORDER BY CLAUSE

SELECT * FROM AssetManufacturerName ORDER BY TRIM(AssetManufacturerName)

But this still does not produce desired output as shown below. Because of leading spaces Lenovo is on top. I want it to be on 2nd.

Query Result

Sample Table
+-------------+----------------+
|    ID       |     Name       |
+-------------+----------------+
|    01       | ' Robert King' |
|             |                |
|    02       | 'Arsim Kip'    |
+-------------+----------------+

I gone thorough this question but no joys. I thinks this is MYSQL version issue.

NOTE: Our Application is working on MYSQL 5.5.52.

Solution

  • The trim function may work as intended. I suspect it could be different character like tab.

    ORDER BY TRIM(TRIM(BOTH '\t' FROM AssetManufacturerName))

    db<>fiddle demo