Archive

Archive for January, 2011

Sorting Strings Containing Numbers

January 20th, 2011 No comments

Sorting strings does not require a lot of coding in .NET or SQL. However when the strings contain related numbers, the sort order is not ‘logical’ according to the numeric sequence. E.g.

label-8-x-99
label-8-x-100
label-9-x-100
label-10-x-9

Would be sorted as:

label-10-x-9
label-8-x-100
label-8-x-99
label-9-x-100

Which is not according to the numeric sequence. Here is a method in C# which can be used for converting strings to they can be sorted in numeric order. Note that the numbers considered can have up to 10 digits in this case:

public static string NumericSortableString( string s )
{
    if ( String.IsNullOrEmpty( s ) )
        return s;
 
    const int MAX_DIGITS = 10;
    StringBuilder sb = new StringBuilder( s.Length );
    int iDigits = 0;
 
    for ( int i = s.Length - 1; i >= 0; --i )
    {
        if ( s[ i ] >= '0' && s[ i ] <= '9' )
            ++iDigits;
        else if ( iDigits > 0 )
        {
            for ( int j = iDigits; j < MAX_DIGITS; ++j )
                sb.Insert( 0, '0' );
                    iDigits = 0;
        }
        sb.Insert( 0, s[ i ] );
    }
    if ( iDigits > 0 )
        for ( int j = iDigits; j < MAX_DIGITS; ++j )
            sb.Insert( 0, '0' );
    return sb.ToString();
}

The same can be achieved on SQL Server using the following T-SQL Scalar valued function:

ALTER FUNCTION [dbo].[NumericSortableString]( @s NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS 
BEGIN
    DECLARE @o NVARCHAR(MAX);
    DECLARE @c NCHAR(1);
    DECLARE @Digits INT, @MaxDigits INT, @i INT;
 
    IF @s IS NULL OR @s = ''
        RETURN @s;
 
    SET @Digits = 0;
    SET @MaxDigits = 10;
    SET @i = LEN( @s );
    SET @o = '';
 
    WHILE @i > 0
    BEGIN
        SET @c = SUBSTRING( @s, @i, 1 );
        IF @c >= '0' AND @c <= '9'
            SET @Digits = @Digits + 1;
        ELSE IF @Digits > 0
        BEGIN
            IF @Digits < @MaxDigits
                SET @o = REPLICATE( '0', @MaxDigits - @Digits ) + @o;
            SET @Digits = 0;
        END
        SET @o = @c + @o;
        SET @i = @i - 1;
    END
    IF @Digits > 0 AND @Digits < @MaxDigits
        SET @o = REPLICATE( '0', @MaxDigits - @Digits ) + @o;
    RETURN @o;
END
Categories: C#, Software Development, SQL Tags: , , , , , , , , , ,