MySql sort order varchar as int, starting with character

Question

i have column 'name' in my db where client enters values like:

sample 1, sample 2, sample 101, sample 11, sample 20, sample 100, some sample, active sample, etc

so, when i want to order data by this column result is:

active sample
sample 1
sample 100
sample 101
sample 11
sample 2
sample 20
some sample 

instead

active sample
sample 1
sample 2
sample 11
sample 20
sample 100
sample 101
some sample

So, data never starts as number, it sometimes ends with number, but sometimes it doesn't conatain number at all. It is important to order alphabetically, but when data conatains same prefix it should order values as int.

Thanks in advance.

Answer 1

Breaking this extremely ugly piece of code down...

Take the initial value and test to see if it contains a ' ' character. If yes then we start splitting on the index of that ' ' character, if no then just return the initial value. Begin recursion for each additional possibility of ' ' delimited string. This is built out to allow for 2 ' ' delimited words followed by a numeric value, or not as the case may be.

SQL Fiddle here: http://sqlfiddle.com/#!2/70168/7

Query:

SELECT 
   sampleInfo,

   IF (LOCATE(' ', sampleInfo) > 0,
       SUBSTRING(sampleInfo, 1, LOCATE(' ', sampleInfo) - 1),
       sampleInfo) AS firstPart,

   IF (LOCATE(' ', sampleInfo) > 0,
       IF (LOCATE(' ', SUBSTRING(sampleInfo, LOCATE(' ', sampleInfo) + 1)) > 0,          
           SUBSTRING(
             SUBSTRING(sampleInfo, LOCATE(' ', sampleInfo) + 1),
             1,
             LOCATE(' ', SUBSTRING(sampleInfo, LOCATE(' ', sampleInfo) + 1)) - 1),
           SUBSTRING(sampleInfo, LOCATE(' ', sampleInfo) + 1)),
           0) AS secondPart,

   IF (LOCATE(' ', sampleInfo) > 0,
       IF (LOCATE(' ', SUBSTRING(sampleInfo, LOCATE(' ', sampleInfo) + 1)) > 0,
           SUBSTRING(
             SUBSTRING(sampleInfo, LOCATE(' ', sampleInfo) + 1),
             LOCATE(' ', SUBSTRING(sampleInfo, LOCATE(' ', sampleInfo) + 1)) + 1),
           0),
       0) AS thirdPart    

FROM test
ORDER BY 
  firstPart,
  thirdPart,
  CONVERT(secondPart, SIGNED) --if conversion fails the value is 0

Results:

╔═══════════════╦═══════════╦════════════╦═══════════╗
║  SAMPLEINFO   ║ FIRSTPART ║ SECONDPART ║ THIRDPART ║
╠═══════════════╬═══════════╬════════════╬═══════════╣
║ asdf 1        ║ asdf      ║ 1          ║         0 ║
║ asdf 2        ║ asdf      ║ 2          ║         0 ║
║ sample        ║ sample    ║ 0          ║         0 ║
║ sample 1      ║ sample    ║ 1          ║         0 ║
║ sample 2      ║ sample    ║ 2          ║         0 ║
║ sample 11     ║ sample    ║ 11         ║         0 ║
║ sample 20     ║ sample    ║ 20         ║         0 ║
║ sample 100    ║ sample    ║ 100        ║         0 ║
║ sample 101    ║ sample    ║ 101        ║         0 ║
║ sample test 1 ║ sample    ║ test       ║         1 ║
║ sample test 2 ║ sample    ║ test       ║         2 ║
╚═══════════════╩═══════════╩════════════╩═══════════╝

Disclaimer: This solution isn't very performant or extensible. You would be far better served by doing this SUBSTRING work via another language such as PHP or C# for a couple examples. This also makes a couple assumptions about your data based on what you stated in your comments.