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