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: .NET, C++, Number, Numeric, Padding, Sortierung, Sorting, SQL Server, SQL.T-SQL, String, UDF
When working with dynamic sorting e.g. when using a GridView/ObjectDataSource combination which will sort according to the column header clicked, the most intuitive way of realizing sorting behavoiur in the ObjectDataSource is by using a large if/switch construct.
A smarter way is to use custom lambda expressions with Linq, which will automatically construct the correct Linq query according to the column name and sort order. No matter how many columns, whether sorted ascending or descending, the following code will handle it all:
/// <summary>This Linq helper class constructs a custom lambda expression used for sorting.</summary>
public static class LinqExtensions
{
/// <summary>Generic method used for sorting according to the pseudo SQL syntax emmitted by ObjectDataSource.</summary>
public static IQueryable<T> OrderBySQLSyntax<T>( this IQueryable<T> source, string sPropertyParameter )
{
string[] asParams = sPropertyParameter.Split( new char[] { ' ' }, 2 );
bool descending = ( asParams.Length == 2 && String.Equals( asParams[ 1 ], "DESC" ) );
return (IQueryable<T>)OrderByExtension( source, asParams[ 0 ], descending );
}
/// <summary>Used for sorting in ascending/descending order according to the property provided.</summary>
public static IQueryable OrderByExtension( this IQueryable source, string propertyName, bool descending )
{
var x = Expression.Parameter( source.ElementType, "x" );
var selector = Expression.Lambda( Expression.PropertyOrField( x, propertyName ), x );
MethodCallExpression mce = Expression.Call( typeof( Queryable ), descending ? "OrderByDescending" : "OrderBy",
new Type[] { source.ElementType, selector.Body.Type }, source.Expression, selector );
return source.Provider.CreateQuery( mce );
}
} |
Although SQLite does not contain builtin support for directly writing user defined functions (UDF) in PL/SQL, it features a very fast and convenient way for extended functionality using your programming language of choice.
Let me illustrate this by example: In this case we are looking at a string transformation algorithm that allows for sorting a Url string according to the top level domain, domain name and host name. E.g. http://www.wikipedia.de/favicon.ico should be sorted according to http://de.wikipedia.www/favicon.ico.
Using PL/SQL with its limited string processing functions, this can result in a lot of coding. Using .NET, it’s easy to write and surprisingly fast.
[SQLiteFunction( Name = "ReverseDomain", Arguments = 1, FuncType = FunctionType.Scalar )]
public class ReverseDomain: SQLiteFunction
{
public override object Invoke( object[] args )
{
if ( args == null || args.Length < 1 || args[ 0 ] == null )
return null;
string[] ss = args[ 0 ].ToString().Split( new string[] { "://", "/" }, 3 );
if ( ss.Length > 1 )
{
string[] sss = ss[ 1 ].Split( new char[] { '.' } );
Array.Reverse( sss );
return ss[ 0 ] + "://" + String.Join( ".", sss ) + "/" + ( ss.Length == 3 ? ss[ 2 ] : String.Empty );
}
return args[ 0 ];
}
} |
Alternatively, we could use the UriBuilder class:
[SQLiteFunction( Name = "ReverseDomain", Arguments = 1, FuncType = FunctionType.Scalar )]
public class ReverseDomain: SQLiteFunction
{
public override object Invoke( object[] args )
{
if ( args == null || args.Length < 1 || args[ 0 ] == null )
return null;
UriBuilder u = new UriBuilder( args[ 0 ] as string );
string[] ss = u.Host.Split( new char[] { '.' } );
Array.Reverse( ss );
u.Host = String.Join( ".", ss );
return u.ToString();
}
} |
Where is the catch with UriBuilder? Compared to the custom string splitting solution it is about 5 times slower. It can transform only about 65000 urls per second on my machine, while the prior method can convert 350000 urls in the same time.
How is this new UDF called from SQL?
SELECT * FROM UrlTable ORDER BY ReverseDomain( Url ) |
Categories: Software Development Tags: .NET, C++, Callback, Domain, Reverse, Sorting, SQLite, SQLiteFunction, UDF, Uri, UriBuilder, Url