I'm trying to sort a field that has letters, numbers and quotes but I can't get the results in order. A field (named name) in a table has data like this, though not ordered as shown:
6"w x 9"h
6"w x 10"h
7"w x 8"h
7"w x 9"h
7"w x 10"h
7"w x 21"h
10"w x 10"h
The command I'm using is
select name from my_table order by name;
The result is
10"w x 10"h
6"w x 10"h
6"w x 9"h
7"w x 10"h
7"w x 21"h
7"w x 8"h
7"w x 9"h
I've tried all of the following that I found on this site. I couldn't get the last one to work at all but the others worked a little better than the above but still not correct.
order by name * 1
order by name + 0
order by CAST(name AS DECIMAL(10,2))
order by length(name), name
order by CAST(SUBSTR(name, 0, LOCATE('w', name) - 1) AS int),
CAST(SUBSTR(name FROM (LOCATE('h', name) - 1)) AS int)
The first two alternatives above gave this output so they are almost doing it.
6"w x 9"h
6"w x 10"h
7"w x 10"h
7"w x 21"h
7"w x 9"h
7"w x 8"h
10"w x 10"h
Does anyone know how to sort these so they are in proper order, like below.
6"w x 9"h
6"w x 10"h
7"w x 8"h
7"w x 9"h
7"w x 10"h
7"w x 21"h
10"w x 10"h
The last one is in the right direction. You need to order by the number in the string:
ORDER BY CAST(SUBSTR(name, 1, LOCATE('"w', name) - 1) AS signed),
CAST(SUBSTR(name, LOCATE('x', name) + 1, LOCATE('"h', name) - LOCATE('x', name) -1) AS signed)