How to sort string which contains numbers and alphabets?

Question

I have qt_no values such as

AM1,M3,M4,M14,M30,M40,MA01,A10,A13,A07,B01,B10,Z33,Z13

etc (really any int 2-3 digits after a letter).

I have tried sorting as

order by length(qt_no), qt_no

It doesn't reach my required output.

My expected output is

A01,A07,A10,A13,B01,AM1,M3,M4,M14,M30,M40,MA01,Z13,Z33

Remember these qt_no values are of the same field and a different row of the same table.

I have no idea what to do from here on.

Any help will be appreciated.

EDIT

Here is a sample database to play with.

varchar(10), but I researched some, realised that, I need natural sort order — Rahul, Oct 24 '18 at 12:10
Please create a SQL Fiddle or DB Fiddle and add it to the question, for others to play with. — Madhur Bhaiya, Oct 24 '18 at 12:27
You can use the function created in this answer: https://stackoverflow.com/a/37269038/2469308 and get numbers out of the string and non-numeric part out of the string. — Madhur Bhaiya, Oct 24 '18 at 12:56
Check this Fiddle: https://www.db-fiddle.com/f/e5bEtYDzMqqvNRCs3qN6iD/1 Does it work for you ? — Madhur Bhaiya, Oct 24 '18 at 13:07

Amadan · Accepted Answer · 2018-10-24T12:30:27.833

4

The best scenario is creating two extra columns, one for the alphabetic part, one for the numeric part; then it is as simple as ORDER BY alpha_part ASC, num_part ASC. If you have a joint index on those two columns, it will also be very fast.

If you absolutely have to parse the column at query time, that takes time - and also makes indices useless, which makes everything so much slower. But you can do this:

...
ORDER BY
  REGEXP_REPLACE(qt_no, '\d+', '') ASC, 
  CAST(REGEXP_REPLACE(qt_no, '\D+', '') AS INTEGER) ASC

EDIT: I'm very sorry, but I have no idea how to do it on 5.7 except like this:

SELECT qt_no FROM t
ORDER BY
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(qt_no, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '') ASC, 
CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(qt_no, 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', ''), 'G', ''), 'H', ''), 'I', ''), 'J', ''), 'K', ''), 'L', ''), 'M', ''), 'N', ''), 'O', ''), 'P', ''), 'Q', ''), 'R', ''), 'S', ''), 'T', ''), 'U', ''), 'V', ''), 'W', ''), 'X', ''), 'Y', ''), 'Z', '') AS UNSIGNED) ASC;

edited Oct 24 '18 at 12:30

answered Oct 24 '18 at 11:54

Amadan

191,408
23
240
301

It is showing me error. `REGEXP_REPLACE does not exist ` – Rahul Oct 24 '18 at 12:07
Probably old database. `REGEXP_REPLACE` is available in 8.0... Can you specify which version you're running? EDIT: I just saw in another comment, 5.7 – Amadan Oct 24 '18 at 12:19
Yes. My server's mysql version is 5.7 can you atleast give me query to fetch letters only and numbers only as two different fields and I will sort on those two fields. Because database is untouchable to create two separate fields. I hope you understand – Rahul Oct 24 '18 at 12:20
I would have implemented your edit portion. But its still wont work – Rahul Oct 24 '18 at 13:15
I just ran it in your fiddle, no error: https://www.db-fiddle.com/f/e5bEtYDzMqqvNRCs3qN6iD/2 – Amadan Oct 24 '18 at 13:18
here is real problem. [link](https://rextester.com/WTKRT58854). You will get what my concern is – Rahul Oct 24 '18 at 13:55
Sorry, I don't... you need to describe what I'm looking at. – Amadan Oct 25 '18 at 00:56
You just need to add a `replace` for the `-` in the letters. https://rextester.com/RLYI98860 – xQbert Oct 25 '18 at 12:29

score 3 · Answer 2 · answered Oct 24 '18 at 13:22

Due to lack of Regex function in MySQL version < 8.0, we can create a Custom function to extract numeric substring out of a given string.

Following is a modified function from this answer, which returns the integer value from an input string. The modification done here is that it returns string instead of Int. Because you have numeric strings like 07, which needs to be returned as it is, instead of 7.

DELIMITER $$

CREATE FUNCTION `ExtractNumber`(in_string VARCHAR(50)) 
RETURNS VARCHAR(50)
NO SQL
BEGIN
    DECLARE ctrNumber VARCHAR(50);
    DECLARE finNumber VARCHAR(50) DEFAULT '';
    DECLARE sChar VARCHAR(1);
    DECLARE inti INTEGER DEFAULT 1;

    IF LENGTH(in_string) > 0 THEN
        WHILE(inti <= LENGTH(in_string)) DO
            SET sChar = SUBSTRING(in_string, inti, 1);
            SET ctrNumber = FIND_IN_SET(sChar, '0,1,2,3,4,5,6,7,8,9'); 
            IF ctrNumber > 0 THEN
                SET finNumber = CONCAT(finNumber, sChar);
            END IF;
            SET inti = inti + 1;
        END WHILE;
        RETURN finNumber;
    ELSE
        RETURN '';
    END IF;    
END$$

DELIMITER ;

Now, you can use this custom function, and sort by alphabetic part, and then numeric part (Casted to unsigned).

SELECT id, 
       name,
       REPLACE(name, ExtractNumber(name), '') as strpart, 
       CAST(ExtractNumber(name) AS UNSIGNED) as numpart 
FROM test
ORDER BY strpart, 
         numpart

DB Fiddle DEMO

xQbert · Answer 3 · 2018-10-24T13:34:06.433

Not pretty and as already stated since we're string parsing, it will be slow. This assumes your format is Alpha then numeric values NEVER mixed. It finds the first numeric value and then splits into two columns based on that finding.

DEMO:

I just wanted to sort by qt_no+0 to get the natural sort but that didn't work. So I went down a different path....

Field1 is your qt_no field...

SELECT Field1,

#Use this to just get the number values but since we lose trailing zeros...Step 2 we reverse the value so numbers are first allowing the convert to drop the letters.  unfortunately this also drops the trailing (leading since we reversed) zeros.
       @NumStep1 := reverse(CONVERT(reverse(Field1), SIGNED)) NumStep1,

#We got the postiion of the first number... so get the  whole number now.
       @NumStep2 :=substring(Field1,locate(@numStep1,Field1),length(Field1)) NumStep2,
       @Alpha:= substring(Field1,1,Locate(@numStep2,Field1)-1) Alpha

FROM (
SELECT 'AM1' as Field1 UNION ALL 
SELECT 'M3' as Field1 UNION ALL
SELECT 'M4' as Field1 UNION ALL
SELECT 'M14' as Field1 UNION ALL
SELECT 'M30' as Field1 UNION ALL
SELECT 'M40' as Field1 UNION ALL
SELECT 'MA01' as Field1 UNION ALL
SELECT 'A10' as Field1 UNION ALL
SELECT 'A13' as Field1 UNION ALL
SELECT 'A07' as Field1 UNION ALL
SELECT 'B01' as Field1 UNION ALL
SELECT 'B10' as Field1 UNION ALL
SELECT 'Z33' as Field1 UNION ALL
SELECT 'Z13' as Field1) Z
ORDER BY Alpha, NumStep2*1

Gives us:

+----+--------+----------+----------+-------+
|    | Field1 | NumStep1 | NumStep2 | Alpha |
+----+--------+----------+----------+-------+
|  1 | A10    |        1 |       10 | A     |
|  2 | A13    |       13 |       13 | A     |
|  3 | A07    |       07 |       07 | A     |
|  4 | AM1    |        1 |        1 | AM    |
|  5 | B10    |        1 |       10 | B     |
|  6 | B01    |       01 |       01 | B     |
|  7 | M3     |        3 |        3 | M     |
|  8 | M4     |        4 |        4 | M     |
|  9 | M14    |       14 |       14 | M     |
| 10 | M30    |        3 |       30 | M     |
| 11 | M40    |        4 |       40 | M     |
| 12 | MA01   |       01 |       01 | MA    |
| 13 | Z33    |       33 |       33 | Z     |
| 14 | Z13    |       13 |       13 | Z     |
+----+--------+----------+----------+-------+

Without User variables but splitting data into alpha & numeric.

SELECT Field1,
       substring(Field1,locate(reverse(CONVERT(reverse(Field1), SIGNED)),Field1),length(Field1)) NumStep2,
       substring(Field1,1,Locate(substring(Field1,locate(reverse(CONVERT(reverse(Field1), SIGNED)),Field1),length(Field1)),Field1)-1) Alpha

FROM (
SELECT 'AM1' as Field1 UNION ALL 
SELECT 'M3' as Field1 UNION ALL
SELECT 'M4' as Field1 UNION ALL
SELECT 'M14' as Field1 UNION ALL
SELECT 'M30' as Field1 UNION ALL
SELECT 'M40' as Field1 UNION ALL
SELECT 'MA01' as Field1 UNION ALL
SELECT 'A10' as Field1 UNION ALL
SELECT 'A13' as Field1 UNION ALL
SELECT 'A07' as Field1 UNION ALL
SELECT 'B01' as Field1 UNION ALL
SELECT 'B10' as Field1 UNION ALL
SELECT 'Z33' as Field1 UNION ALL
SELECT 'Z13' as Field1) Z
ORDER BY Alpha, NumStep2*1

Yep, tried a different variant using user variables to show what's going on, could really just embed each formula — xQbert, Oct 24 '18 at 13:22

score 0 · Answer 4 · answered Oct 24 '18 at 12:03

0

You can divide the order by into three phases: first the letter, then the length of the string and finally for it's alphabetical order:

select * from test order by substring( qt_no, 1, 1 ), length(qt_no), qt_no;

answered Oct 24 '18 at 12:03

Guilherme B

75
1
6

sorry mate. But it is uncertain the number of letters or alphabets may come in a string. I need natural sort order – Rahul Oct 24 '18 at 12:12
then @Amadan answer is right, you will have to take only the alphabetical letters with an REGEX, and then order by the integrers after, now search for the REGEX function in mysql.. hehe – Guilherme B Oct 24 '18 at 12:15
It is not available in server's mysql version 5.7 – Rahul Oct 24 '18 at 12:17

How to sort string which contains numbers and alphabets?

4 Answers4

DB Fiddle DEMO