mysql sorting varchar fields that contain char and numerical values

Question

I have the following data in SQL:

name       ID  
 A[0]       1  
 A[1]       1  
 A[2]       1  
 AA[0]      2  
 AA[15]     2   
 AA[27]     2

I'd like to sort this data, first by ID and then by name. Sorting by ID is easy since it's an int, the problem is with the name - I get weird values, e.g. a[7],a[27],a[3] when I'm trying to sort numerically. When sorting alphbetically I get what you'd expect, a[10],a[11]...a[19],a[2] etc
I assume this is because the field is a varchar and has both numerical and textual parts. Is there any way to sort this? splitting it into substrings seems like a bad option since the length of each part (the textual and numerical) isn't constant.
Thanks!

what is your select statement look like? – Hituptony Feb 18 '13 at 14:15 — Hituptony, Feb 18 '13 at 14:15

score 1 · Accepted Answer · answered Feb 18 '13 at 14:17

1

for your requirement , you may consider splitting column to two parts( i.e. A[20] would become A and 20 ) and order by combination of generated splitted value.

So your query will look like order by id, name-first part (obtained by splitting or getting substring),to_number(name-second part)

answered Feb 18 '13 at 14:17

dillip

1,782
17
16

I was hoping for a more straight forward way :( But yeah, if nothing else will work I'll do that – n00b programmer Feb 18 '13 at 14:20

score -2 · Answer 2 · edited May 23 '17 at 11:49

-2

Okay, you would want to either create a function like this use or use a substring on the name field

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

see reference - http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

But also should be noted that if you find yourself having to split columns up, this is going to make query performance issues for you in future because underlying database design is flawed. I would re-create that table using the split function and dividing that up into two columns. There are three levels of normalized databases that you should read up on before moving on here.

also - Split value from one field to two

edited May 23 '17 at 11:49

Community

1
1

answered Feb 18 '13 at 14:14

Hituptony

2,740
3
22
44

I want my sort to return the above list - I already wrote it the way it should look like after sorting. If I order by ID, Name then the sql doesn't consider the number in brackets, it just orders alphabetically – n00b programmer Feb 18 '13 at 14:19
you didnt say that. you said sorting by name you get weird values. it is not clear that you orderd by both at one time, it just says int is not problem and alphabet is. therefore both would be the solution. but anyway let me think of approach – Hituptony Feb 18 '13 at 14:20
"- I get weird values, e.g. a[7],a[27],a[3] when I'm trying to sort numerically. When sorting alphbetically I get what you'd expect, a[10],a[11]...a[19],a[2] etc" The problem is when I try sorting numerically, because the fields have alot of text in them – n00b programmer Feb 18 '13 at 14:29

mysql sorting varchar fields that contain char and numerical values

2 Answers2