How to order a result set in SQL based on the numeric part of the string value

There are occasions where you would like to search in a varchar column and order the results based on the numeric part of the field.

Imagine the following table:

ABC
ABC1
ABC2
ABC3 and so on...

ABC11
ABC12
ABC13 and so on..

ABC20
ABC21
ABC22 and so on..

So basically what I have is any string value (not always ABC, any string value) that can either be followed by the number or it may just be a string without the number.

When you run

select * from table order by my column asc

you get following results:

ABC
ABC1
ABC11
ABC12
ABC13
ABC2
ABC20
ABC21
ABC22
ABC3
ABC31
ABC32

In this scenario, you would need the results sorted by the numeric value and not alphabetically.
Eg:

ABC
ABC1
ABC2
ABC3
ABC11
ABC12
ABC13
ABC20
ABC21
ABC22
ABC31
ABC32

How can this be accomplished?

Continue reading “How to order a result set in SQL based on the numeric part of the string value”