[sqlite] HOWTO: Adding Natural Sort To Sqlite Database...

[tezozomoc]

HOWTO: Adding Natural Sort To Sqlite Database...

Assumption... this is based on SQLITE 2.8.6... forgive the aging..


I will make this very short and sweet... 

1.  Get the following two files:

a.. strnatcmp.c, strnatcmp.h - the algorithm itself from 
a.. 
http://sourcefrog.net/projects/natsort/

add to your sqlite build project, I am using VxWorks, so the way that I did it will 
probably will not make sense...

2.  Open util.c

3.  Replace the following: 
  **
  ** $Id: util.c,v 1.66 2003/07/30 12:34:12 drh Exp $
  */
  #include "sqliteInt.h"
  #include <stdarg.h>
  #include <ctype.h>

 with 

  ** $Id: util.c,v 1.66 2003/07/30 12:34:12 drh Exp $
  */
  #include "sqliteInt.h"
  #include <stdarg.h>
  #include <ctype.h>
  #include "strnatcmp.h"

4.  Find the following function sqliteSortCompare(...)

Replace

      assert( a[0]==b[0] );
      if( (dir=a[0])=='A' || a[0]=='D' ){
       /*-->*/res = strcmp(&a[1],&b[1]);
        if( res ) break;
      }else{
        isNumA = sqliteIsNumber(&a[1]);
        isNumB = sqliteIsNumber(&b[1]);
        if( isNumA ){
          double rA, rB;
          if( !isNumB ){
            res = -1;
            break;
          }
          rA = atof(&a[1]);
          rB = atof(&b[1]);
          if( rA<rB ){
            res = -1;
            break;
          }
          if( rA>rB ){
            res = +1;
            break;
          }
        }else if( isNumB ){
          res = +1;
          break;
        }else{
            /*-->*/res = strcmp(&a[1],&b[1]);
          if( res ) break;
        }
      }

with 

      assert( a[0]==b[0] );
      if( (dir=a[0])=='A' || a[0]=='D' ){
        /*res = strcmp(&a[1],&b[1]);*/
        res = strnatcmp(&a[1],&b[1]);
        if( res ) break;
      }else{
        isNumA = sqliteIsNumber(&a[1]);
        isNumB = sqliteIsNumber(&b[1]);
        if( isNumA ){
          double rA, rB;
          if( !isNumB ){
            res = -1;
            break;
          }
          rA = atof(&a[1]);
          rB = atof(&b[1]);
          if( rA<rB ){
            res = -1;
            break;
          }
          if( rA>rB ){
            res = +1;
            break;
          }
        }else if( isNumB ){
          res = +1;
          break;
        }else{
            /*res = strcmp(&a[1],&b[1]);*/
            res = strnatcmp(&a[1],&b[1]);
          if( res ) break;
        }
      }

This has made my SQLITE experience much better... no need for COLLATE Function .....

by
Tezozomoc.