Suggestions:
1) Try:
select cli_id, antok(cli_id) as antok_cli_id ...... order by antok_cli_id
This should at least show you exactly what it thinks it sorted.
2) Don't do regcomp() more than once. It can be very costly. And
cache input.c_str() and input.length() while you are at it.
(Actually... should it really be a "string" in the first place?)
3) digits[] may be insufficient in some cases (though not in your
samples).
--David Garfield
ChingChang Hsiao writes:
>
> The purpose of function antok is that solve the problem of "order by cli_id".
>
> Function antok converts,
>
> X86-1 -> x186-01
> X86-2 -> x186-02
> X86-10 -> x186-110
> X86-111 -> x186-2111
> X86-2222 -> x186-32222
> 1.1/12-ds3 -> 01.01/112-ds03
>
> Examples,
> Input cli_id x86-2, x86-10, x86-1
>
> Order by cli_id
> X86-1
> X86-10
> X86-2
>
> Order by antok(cli_id)
> X86-2
> X86-1
> X86-10
>
> Antok solves the problem of x86-1,x86-10 sorting, but it didn't sort x86-2,
> x86-1(input order).
>
> My solution is,
>
> order by antok(cli_id) asc,cli_id asc
>
> X86-1
> X86-2
> X86-10
>
>
> But It solves the same category, there are still problems for alphabet shown
> as below.
>
> 0.1
> 0.2
> 0.3
> 0.4
> 0.5
> x86-1
> x86-2
> chdlc-1
> chdlc-2
> chdlc-3
> x86-10
>
> It should be,
>
> 0.1
> 0.2
> 0.3
> 0.4
> 0.5
> chdlc-1
> chdlc-2
> chdlc-3
> x86-1
> x86-2
> x86-10
>
> Do you know why x86-1,x86-2 jump ahead chdlc-1 when I use "order by
> antok(cli_id)" or "order by antok(cli_id) asc,cli_id asc"?
>
>
> ChingChang
>
> Here are the source code shown as below.
>
> int32
> DbHandle::registerOvnFuncs( sqlite3* db ) {
>
> // Add a alpha numeric token generator
> sqlite3_create_function(db, "antok", 1, SQLITE_UTF8, NULL,
> &dbAnTokenFunc, NULL, NULL);
> return 0;
> }
>
> static void
> dbAnTokenFunc( sqlite3_context *context, int argc, sqlite3_value **argv )
> {
> assert( argc==1 );
> switch( sqlite3_value_type(argv[0]) ){
> case SQLITE_INTEGER:
> sqlite3_result_int64( context, sqlite3_value_int64(argv[0]) );
> break;
> case SQLITE_NULL:
> sqlite3_result_null( context );
> break;
> case SQLITE_TEXT:
> default:
> SqlSortHelper sqlSortHelper;
> string token;
> token = sqlSortHelper.GetAlphaNumericOrderToken(
> (char*)sqlite3_value_text(argv[0]) );
> sqlite3_result_text( context, token.c_str(), token.length(), NULL );
> break;
> }
> }
>
> #define POTENTIAL_MATCHES 1
>
> string SqlSortHelper::GetAlphaNumericOrderToken( string input ) {
> regex_t re;
> regmatch_t pmatch[POTENTIAL_MATCHES];
> string token;
> uint32 pos = 0;
> char digits[10] = "012345678";
>
> memset( pmatch, -1, sizeof(pmatch) );
> regcomp( &re, "[0-9]+", REG_EXTENDED);
> while (pos < input.length())
> {
> regexec( &re, input.c_str()+pos, 1, pmatch, 0);
> if ( pmatch[0].rm_so != -1 )
> {
> token.append( (input.c_str() + pos), pmatch[0].rm_so);
> token.push_back( digits[((pmatch[0].rm_eo-pmatch[0].rm_so)-1)] );
> token.append( (input.c_str() + pos + pmatch[0].rm_so),
> pmatch[0].rm_eo - pmatch[0].rm_so );
> pos = pos + pmatch[0].rm_eo;
> }
> else
> {
> if (pos == 0) {
> regfree( &re);
> return input;
> }
> else
> break;
> }
> }
> if (pos < input.length())
> token.append( (input.c_str() + pos), input.length()-pos );
> regfree( &re);
> return token;
> }
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
