Write MS SQL Function

Hello, I need to build a MS SQL 2008 Function (I think I need a Function). I have a table with the following data (see attached): ID | Reference | Category ID = Guid Reference 0 varchar(20) Category = Tinyint Inside a specific Category (for example WHERE Category = 1), I need to sort the data DESC using the "Reference" column; the problem is that this column is string, so standard sorting is wrong (for example, A100 comes before A 10). In other words, I need to display the data in a sorted way within the same Category. For example, if I choose Category = 3, I should have: A419 A418 A417 ... A01 The Function should accept in input a parameter specifying the Category I want to sort (sorting is always DESC). According to the Category type, you can "understand" how to sort data. We can have 11 category types: 0 = the reference is composed of numbers only 1 = the reference is composed of numbers only followed by "/" and a number (sorting should be on the left number) 2 = the reference is composed of numbers only followed by "/" and a letter (sorting should be on the left number) 3 = the reference is composed of "A" followed by a number 4 = the reference is composed of "ST" followed by a number 5 = the reference is composed of "R" followed by a number 6 = the reference is composed of "S" followed by a number 7 = the reference is composed of "E" followed by a number 8 = the reference is composed of "O" followed by a number 9 = the reference is composed of "SP" followed by a number 99 = Unkown I'm attaching the c# code I used to map the Category from the Reference, maybe this code may inspire you (I think a similar code should be written in SQL to get the "numeric" part of the Reference and then sort DESC by it). From an SQL point of view, when your Function is ready I'd like to write something like: SELECT * FROM Products WHERE Category = 3 ORDER BY YourFunction(3) For any questions, just ask. *** I need this project to be completed in max 3 hours from now ***