Sort Fields which contains both Numeric and Characters

It was a long time since my last post. Finally I got something to write about. Store Procedures … something I hate most of the time. But today I felt good about them because I was able to find a solution for a sorting issue.
The particular column contained data like “98ABC-76543-DEF-12345-GHI10” and also “GHJ23”. I was supposed to sort this field on Numeric value for the last part in red. And for the other parts it should be treated as Numeric for the Numeric parts and as Strings for the others.
So what should I do was to split all parts and sort them separately and get numeric part in red and sort.

But wait a minute do we have to all the things??? I don’t think so... I have found (actually one of my friends) an easy way to do that. Here it is.

SELECT [name],        
        RANK() OVER (ORDER BY         
                       CAST(PARSENAME(modifiedName, 5) AS  nvarchar ),   
                       CAST(PARSENAME(modifiedName, 4) AS  INT ),  
                       CAST(PARSENAME(modifiedName, 3) AS  nvarchar ),
                       CAST(PARSENAME(modifiedName, 2) AS  INT ),         
                       CAST(dbo.fFilterNumeric (PARSENAME(modifiedName, 1)) AS  INT ) 
                     ) AS sorted_name 
  FROM (SELECT [name],
               REPLACE([Name], '-', '.') AS modifiedName        
          FROM myTestTable) AS myTestTableSorted 

OK the next thing is to explin what I have done here. First PARSENAME gets the string part as for the index from the end of the string. Since the PARSENAME is working only with '.' I had to replace the '-' first. Then for the last part it should be sorted as for the numeric part. For that I have found a function written by IndianScorpion on forums.asp.net to do that.

CREATE FUNCTION [dbo].fFilterNumeric (
       @Src NVARCHAR (255)) 
       RETURNS NVARCHAR (255) 
       AS 
       BEGIN 
           declare @Res nvarchar(255) 
           declare @i int, 
           @l int, 
           @c char 
           select @i=1, 
                  @l=len(@Src) 
           SET @Res = '' 
           while @i<=@l 
              begin    
                  set @c=upper(substring(@Src,@i,1))     
                  IF  isnumeric(@c)=1         
                       SET @Res = @Res + @c    
                       set @i=@i+1 
              end   
           return(@res) 
        END