SQL sort a column(varchar(255)) with numeric sort with alphanumeric data

Question

I am using SQL server(MySQL Ver 14.14 Distrib 5.7.21, for Linux (x86_64)). I want to sort data like this.

DocTyp-2649
DocTyp-2650
DocTyp-2651
DocTyp-2652
DocTyp-26036
DocTyp-26037
DocTyp-26038

my query is

SELECT doc_unique_id FROM docs ORDER BY doc_unique_id ASC

my result is

DocTyp-26036
DocTyp-26037
DocTyp-26038
DocTyp-2649
DocTyp-2650
DocTyp-2651
DocTyp-2652

what can u do?

Use SUBSTRING to split the column in two parts and sort by 1st part, length(2nd part), nd part. This avoids typecast errors when 2nd part is not always numeric — dnoeth
– dnoeth, Commented Feb 9, 2018 at 7:41

uzi · Accepted Answer · 2018-02-09 07:17:30Z

1

Try this query

SELECT doc_unique_id 
FROM docs 
ORDER BY cast(replace(doc_unique_id, 'DocTyp-', '') as int)

answered Feb 9, 2018 at 7:17

uzi

4,1561 gold badge18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yogesh Sharma · Accepted Answer · 2018-02-09 07:18:38Z

1

You could explicitly sort the data by using substring() function (SQL Server) to get the numerical data

select * 
from table 
ORDER BY 
cast(substring(doc_unique_id, charindex('-', doc_unique_id)+1, len(doc_unique_id)) as int)

answered Feb 9, 2018 at 7:18

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Comments

sk11z00 · Accepted Answer · 2018-02-09 07:27:02Z

1

Try this:

select doc_unique_id from docs ORDER BY CAST(SUBSTRING_INDEX(doc_unique_id, '-', -1) AS UNSIGNED) ASC;

answered Feb 9, 2018 at 7:27

sk11z00

1241 silver badge6 bronze badges

Comments

Anil Soman · Accepted Answer · 2018-02-09 08:25:41Z

0

ORDER BY length(doc_unique_id), doc_unique_id ASC

edited Feb 9, 2018 at 8:25

answered Feb 9, 2018 at 7:19

Anil Soman

2,4677 gold badges41 silver badges68 bronze badges

2 Comments

VR Patel Over a year ago

Error Code: 1305 FUNCTION test.len does not exist

Anil Soman Over a year ago

As you are using MySQL, you can try LENGTH instead of LEN?

Collectives™ on Stack Overflow

SQL sort a column(varchar(255)) with numeric sort with alphanumeric data

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related