MySQL TRIM leading space in order by clause issue

Question

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.

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.

let me check it. @LukaszSzozda meanwhile post it as answer so i can accept it as answer if it works for me. — Mobeen Sarwar, Dec 24 '19 at 21:04
The answer really is - you shouldn't have spaces, tabs etc prefixing or suffixing your data. Run an update on the entire table to remove that garbage and fix application that does it, so that only trimmed values are inserted — T.S., Dec 24 '19 at 21:09

Lukasz Szozda · Accepted Answer · 2019-12-24T21:15:43.663

1

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

edited Dec 24 '19 at 21:15

answered Dec 24 '19 at 21:07

Lukasz Szozda

162,964
23
234
275

2

@MobeenSarwar keep in mind that when you use functions like this in WHERE and ORDER BY, if you have an index on the column involved, it will be ignored – T.S. Dec 24 '19 at 21:12
@T.S. please can you suggest some other solution for it. im still looking for it – Mobeen Sarwar Dec 24 '19 at 21:16
@MobeenSarwar You already said that updating table is not an option. What I said has to come in conjunction of updating the table. Your data in the table should not have spaces or other "empty" characters at the beginning or end of the string. If you do, you are forced to apply these string-modifying functions. But when you do that, if you have an index on this field, it will not work. – T.S. Dec 24 '19 at 22:42
1

@LukaszSzozda thanks your solution worked in our production env. I just implemented it. – Mobeen Sarwar Dec 27 '19 at 10:06

MySQL TRIM leading space in order by clause issue

1 Answers1