Advanced Search

Alpha Numeric Sorting in MySQL

/ Published in: MySQL
Save to your folder(s)
Okay,

If it is required to sort columns like this

1A
1a
10A
9B
21C
1C
1D

And Our desired Result is

1A
1C
1D
1a
9B
10A
21C

We can use casting, but casting will not sort 1A, 1B,1C properly. To overcome this issue we are first converting this column to binary and sorting the binary combined with tbl_column.


hehe,,, after doing some headbanging just did it with casting too... and using same logic... damn why I didnt thought of that.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Bin Way
  2. ===================================
  3. SELECT 
  4. tbl_column, 
  5. BIN(tbl_column) AS binray_not_needed_column
  6. FROM db_table
  7. ORDER BY binray_not_needed_column ASC , tbl_column ASC
  8.  
  9. -----------------------
  10.  
  11. Cast Way
  12. ===================================
  13. SELECT 
  14. tbl_column, 
  15. CAST(tbl_column as SIGNED) AS casted_column
  16. FROM db_table
  17. ORDER BY casted_column ASC , tbl_column ASC

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.