The code below implements a [run-time loadable extension][1] for SQLite
that implements a "MIXED" collating sequence that does what you want, I
think. I hereby toss the code over the wall to the community for testing,
criticism, and discussion. I'm particularly interested in suggests for
a name that is better than "MIXED".
To compile following the [instructions on compiling loadable extensions][2]
found in the documentation.
To load the extension from the [command-line shell] put the shared library
that you compiled in the working directory and enter:
.load ./mixedcoll
To use it, just add "COLLATE mixed" after the ORDER BY clause terms that
you want sorted in "natural" order. Or add COLLATE mixed to terms on your
indexes, or on the columns of your table definitions.
[1]: https://www.sqlite.org/loadext.html
[2]: https://www.sqlite.org/loadext.html#compiling_a_loadable_extension
[3]: https://www.sqlite.org/cli.html
## The Code:
~~~~~
/*
** 2020-03-27
**
** The author disclaims copyright to this source code. In place of
** a legal notice, here is a blessing:
**
** May you do good and not evil.
** May you find forgiveness for yourself and forgive others.
** May you share freely, never taking more than you give.
**
******************************************************************************
**
** Implement a collating sequence that sorts (unsigned) integers embedded
** in the middle of text in numeric order.
*/
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
#include <ctype.h>
/*
** Collating function that compares text byte-by-byte but compares
** digits in numeric order.
*/
static int mixedCollFunc(
void *notUsed,
int nKey1, const void *pKey1,
int nKey2, const void *pKey2
){
const unsigned char *zA = (const unsigned char*)pKey1;
const unsigned char *zB = (const unsigned char*)pKey2;
int i, x;
for(i=0; i<nKey1 && i<nKey2; i++){
x = zA[i] - zB[i];
if( x!=0 ){
int j;
for(j=i; j<nKey1 && j<nKey2 && isdigit(zA[j]) && isdigit(zB[j]); j++){}
if( j<nKey1 && isdigit(zA[j]) ){
return +1;
}else if( j<nKey2 && isdigit(zB[j]) ){
return -1;
}else{
return x;
}
}
}
return nKey1 - nKey2;
}
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_mixedcoll_init(
sqlite3 *db,
char **pzErrMsg,
const sqlite3_api_routines *pApi
){
int rc = SQLITE_OK;
SQLITE_EXTENSION_INIT2(pApi);
(void)pzErrMsg; /* Unused parameter */
rc = sqlite3_create_collation(db, "mixed", SQLITE_UTF8, 0, mixedCollFunc);
return rc;
}
~~~~~