/ Check-in [6fe13eea]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Added TRIM, LTRIM, and RTRIM functions. (CVS 3698)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:6fe13eeade4fc7099fbda1e6520640927c08debc
User & Date: drh 2007-03-17 17:52:42
Context
2007-03-17
18:22
Add documentation of the REPLACE, TRIM, LTRIM, and RTRIM functions. (CVS 3699) check-in: d42c9636 user: drh tags: trunk
17:52
Added TRIM, LTRIM, and RTRIM functions. (CVS 3698) check-in: 6fe13eea user: drh tags: trunk
13:27
First cut at an implementation of the REPLACE() function. We might yet make this a compile-time option or move it into a separate source file. (CVS 3697) check-in: c2fe746e user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/func.c.

    12     12   ** This file contains the C functions that implement various SQL
    13     13   ** functions of SQLite.  
    14     14   **
    15     15   ** There is only one exported symbol in this file - the function
    16     16   ** sqliteRegisterBuildinFunctions() found at the bottom of the file.
    17     17   ** All other code has file scope.
    18     18   **
    19         -** $Id: func.c,v 1.137 2007/03/17 13:27:55 drh Exp $
           19  +** $Id: func.c,v 1.138 2007/03/17 17:52:42 drh Exp $
    20     20   */
    21     21   #include "sqliteInt.h"
    22     22   #include <ctype.h>
    23     23   /* #include <math.h> */
    24     24   #include <stdlib.h>
    25     25   #include <assert.h>
    26     26   #include "vdbeInt.h"
................................................................................
   693    693   
   694    694     assert( argc==3 );
   695    695     if( sqlite3_value_type(argv[0])==SQLITE_NULL ||
   696    696         sqlite3_value_type(argv[1])==SQLITE_NULL ||
   697    697         sqlite3_value_type(argv[2])==SQLITE_NULL ){
   698    698       return;
   699    699     }
   700         -  nStr = sqlite3_value_bytes(argv[0]);
   701    700     zStr = sqlite3_value_text(argv[0]);
   702         -  nPattern = sqlite3_value_bytes(argv[1]);
          701  +  nStr = sqlite3_value_bytes(argv[0]);
   703    702     zPattern = sqlite3_value_text(argv[1]);
   704         -  nRep = sqlite3_value_bytes(argv[2]);
          703  +  nPattern = sqlite3_value_bytes(argv[1]);
   705    704     zRep = sqlite3_value_text(argv[2]);
          705  +  nRep = sqlite3_value_bytes(argv[2]);
   706    706     if( nPattern>=nRep ){
   707    707       nOut = nStr;
   708    708     }else{
   709    709       nOut = (nStr/nPattern + 1)*nRep;
   710    710     }
   711    711     zOut = sqlite3_malloc(nOut+1);
   712    712     if( zOut==0 ) return;
................................................................................
   723    723     memcpy(&zOut[j], &zStr[i], nStr-i);
   724    724     j += nStr - i;
   725    725     assert( j<=nOut );
   726    726     zOut[j] = 0;
   727    727     sqlite3_result_text(context, (char*)zOut, j, sqlite3_free);
   728    728   }
   729    729   
          730  +/*
          731  +** Implementation of the TRIM(), LTRIM(), and RTRIM() functions.
          732  +** The userdata is 0x1 for left trim, 0x2 for right trim, 0x3 for both.
          733  +*/
          734  +static void trimFunc(
          735  +  sqlite3_context *context,
          736  +  int argc,
          737  +  sqlite3_value **argv
          738  +){
          739  +  const unsigned char *zIn;         /* Input string */
          740  +  const unsigned char *zCharSet;    /* Set of characters to trim */
          741  +  int nIn;                          /* Number of bytes in input */
          742  +  int flags;
          743  +  int i;
          744  +  unsigned char cFirst, cNext;
          745  +  if( sqlite3_value_type(argv[0])==SQLITE_NULL ){
          746  +    return;
          747  +  }
          748  +  zIn = sqlite3_value_text(argv[0]);
          749  +  nIn = sqlite3_value_bytes(argv[0]);
          750  +  if( argc==1 ){
          751  +    static const unsigned char zSpace[] = " ";
          752  +    zCharSet = zSpace;
          753  +  }else if( sqlite3_value_type(argv[1])==SQLITE_NULL ){
          754  +    return;
          755  +  }else{
          756  +    zCharSet = sqlite3_value_text(argv[1]);
          757  +  }
          758  +  cFirst = zCharSet[0];
          759  +  if( cFirst ){
          760  +    flags = (int)sqlite3_user_data(context);
          761  +    if( flags & 1 ){
          762  +      for(; nIn>0; nIn--, zIn++){
          763  +        if( cFirst==zIn[0] ) continue;
          764  +        for(i=1; zCharSet[i] && zCharSet[i]!=zIn[0]; i++){}
          765  +        if( zCharSet[i]==0 ) break;
          766  +      }
          767  +    }
          768  +    if( flags & 2 ){
          769  +      for(; nIn>0; nIn--){
          770  +        cNext = zIn[nIn-1];
          771  +        if( cFirst==cNext ) continue;
          772  +        for(i=1; zCharSet[i] && zCharSet[i]!=cNext; i++){}
          773  +        if( zCharSet[i]==0 ) break;
          774  +      }
          775  +    }
          776  +  }
          777  +  sqlite3_result_text(context, (char*)zIn, nIn, SQLITE_TRANSIENT);
          778  +}
   730    779   
   731    780   #ifdef SQLITE_SOUNDEX
   732    781   /*
   733    782   ** Compute the soundex encoding of a word.
   734    783   */
   735    784   static void soundexFunc(
   736    785     sqlite3_context *context,
................................................................................
  1075   1124       ** the only difference between the two being that the sense of the
  1076   1125       ** comparison is inverted. For the max() aggregate, the
  1077   1126       ** sqlite3_user_data() function returns (void *)-1. For min() it
  1078   1127       ** returns (void *)db, where db is the sqlite3* database pointer.
  1079   1128       ** Therefore the next statement sets variable 'max' to 1 for the max()
  1080   1129       ** aggregate, or 0 for min().
  1081   1130       */
  1082         -    max = ((sqlite3_user_data(context)==(void *)-1)?1:0);
         1131  +    max = sqlite3_user_data(context)!=0;
  1083   1132       cmp = sqlite3MemCompare(pBest, pArg, pColl);
  1084   1133       if( (max && cmp<0) || (!max && cmp>0) ){
  1085   1134         sqlite3VdbeMemCopy(pBest, pArg);
  1086   1135       }
  1087   1136     }else{
  1088   1137       sqlite3VdbeMemCopy(pBest, pArg);
  1089   1138     }
................................................................................
  1105   1154   ** functions.  This should be the only routine in this file with
  1106   1155   ** external linkage.
  1107   1156   */
  1108   1157   void sqlite3RegisterBuiltinFunctions(sqlite3 *db){
  1109   1158     static const struct {
  1110   1159        char *zName;
  1111   1160        signed char nArg;
  1112         -     u8 argType;           /* 0: none.  1: db  2: (-1) */
         1161  +     u8 argType;           /* ff: db   1: 0, 2: 1, 3: 2,...  N:  N-1. */
  1113   1162        u8 eTextRep;          /* 1: UTF-16.  0: UTF-8 */
  1114   1163        u8 needCollSeq;
  1115   1164        void (*xFunc)(sqlite3_context*,int,sqlite3_value **);
  1116   1165     } aFuncs[] = {
  1117   1166       { "min",               -1, 0, SQLITE_UTF8,    1, minmaxFunc },
  1118   1167       { "min",                0, 0, SQLITE_UTF8,    1, 0          },
  1119         -    { "max",               -1, 2, SQLITE_UTF8,    1, minmaxFunc },
  1120         -    { "max",                0, 2, SQLITE_UTF8,    1, 0          },
         1168  +    { "max",               -1, 1, SQLITE_UTF8,    1, minmaxFunc },
         1169  +    { "max",                0, 1, SQLITE_UTF8,    1, 0          },
  1121   1170       { "typeof",             1, 0, SQLITE_UTF8,    0, typeofFunc },
  1122   1171       { "length",             1, 0, SQLITE_UTF8,    0, lengthFunc },
  1123   1172       { "substr",             3, 0, SQLITE_UTF8,    0, substrFunc },
  1124   1173   #ifndef SQLITE_OMIT_UTF16
  1125   1174       { "substr",             3, 0, SQLITE_UTF16LE, 0, sqlite3utf16Substr },
  1126   1175   #endif
  1127   1176       { "abs",                1, 0, SQLITE_UTF8,    0, absFunc    },
................................................................................
  1135   1184       { "hex",                1, 0, SQLITE_UTF8,    0, hexFunc    },
  1136   1185       { "ifnull",             2, 0, SQLITE_UTF8,    1, ifnullFunc },
  1137   1186       { "random",            -1, 0, SQLITE_UTF8,    0, randomFunc },
  1138   1187       { "randomblob",         1, 0, SQLITE_UTF8,    0, randomBlob },
  1139   1188       { "nullif",             2, 0, SQLITE_UTF8,    1, nullifFunc },
  1140   1189       { "sqlite_version",     0, 0, SQLITE_UTF8,    0, versionFunc},
  1141   1190       { "quote",              1, 0, SQLITE_UTF8,    0, quoteFunc  },
  1142         -    { "last_insert_rowid",  0, 1, SQLITE_UTF8,    0, last_insert_rowid },
  1143         -    { "changes",            0, 1, SQLITE_UTF8,    0, changes           },
  1144         -    { "total_changes",      0, 1, SQLITE_UTF8,    0, total_changes     },
         1191  +    { "last_insert_rowid",  0, 0xff, SQLITE_UTF8, 0, last_insert_rowid },
         1192  +    { "changes",            0, 0xff, SQLITE_UTF8, 0, changes           },
         1193  +    { "total_changes",      0, 0xff, SQLITE_UTF8, 0, total_changes     },
  1145   1194       { "replace",            3, 0, SQLITE_UTF8,    0, replaceFunc       },
         1195  +    { "ltrim",              1, 1, SQLITE_UTF8,    0, trimFunc          },
         1196  +    { "ltrim",              2, 1, SQLITE_UTF8,    0, trimFunc          },
         1197  +    { "rtrim",              1, 2, SQLITE_UTF8,    0, trimFunc          },
         1198  +    { "rtrim",              2, 2, SQLITE_UTF8,    0, trimFunc          },
         1199  +    { "trim",               1, 3, SQLITE_UTF8,    0, trimFunc          },
         1200  +    { "trim",               2, 3, SQLITE_UTF8,    0, trimFunc          },
  1146   1201   #ifdef SQLITE_SOUNDEX
  1147         -    { "soundex",            1, 0, SQLITE_UTF8, 0, soundexFunc},
         1202  +    { "soundex",            1, 0, SQLITE_UTF8,    0, soundexFunc},
  1148   1203   #endif
  1149   1204   #ifndef SQLITE_OMIT_LOAD_EXTENSION
  1150         -    { "load_extension",     1, 1, SQLITE_UTF8,    0, loadExt },
  1151         -    { "load_extension",     2, 1, SQLITE_UTF8,    0, loadExt },
         1205  +    { "load_extension",     1, 0xff, SQLITE_UTF8, 0, loadExt },
         1206  +    { "load_extension",     2, 0xff, SQLITE_UTF8, 0, loadExt },
  1152   1207   #endif
  1153   1208   #ifdef SQLITE_TEST
  1154         -    { "randstr",               2, 0, SQLITE_UTF8, 0, randStr    },
  1155         -    { "test_destructor",       1, 1, SQLITE_UTF8, 0, test_destructor},
  1156         -    { "test_destructor_count", 0, 0, SQLITE_UTF8, 0, test_destructor_count},
  1157         -    { "test_auxdata",         -1, 0, SQLITE_UTF8, 0, test_auxdata},
  1158         -    { "test_error",            1, 0, SQLITE_UTF8, 0, test_error},
         1209  +    { "randstr",               2, 0,    SQLITE_UTF8, 0, randStr    },
         1210  +    { "test_destructor",       1, 0xff, SQLITE_UTF8, 0, test_destructor},
         1211  +    { "test_destructor_count", 0, 0,    SQLITE_UTF8, 0, test_destructor_count},
         1212  +    { "test_auxdata",         -1, 0,    SQLITE_UTF8, 0, test_auxdata},
         1213  +    { "test_error",            1, 0,    SQLITE_UTF8, 0, test_error},
  1159   1214   #endif
  1160   1215     };
  1161   1216     static const struct {
  1162   1217       char *zName;
  1163   1218       signed char nArg;
  1164   1219       u8 argType;
  1165   1220       u8 needCollSeq;
  1166   1221       void (*xStep)(sqlite3_context*,int,sqlite3_value**);
  1167   1222       void (*xFinalize)(sqlite3_context*);
  1168   1223     } aAggs[] = {
  1169   1224       { "min",    1, 0, 1, minmaxStep,   minMaxFinalize },
  1170         -    { "max",    1, 2, 1, minmaxStep,   minMaxFinalize },
         1225  +    { "max",    1, 1, 1, minmaxStep,   minMaxFinalize },
  1171   1226       { "sum",    1, 0, 0, sumStep,      sumFinalize    },
  1172   1227       { "total",  1, 0, 0, sumStep,      totalFinalize    },
  1173   1228       { "avg",    1, 0, 0, sumStep,      avgFinalize    },
  1174   1229       { "count",  0, 0, 0, countStep,    countFinalize  },
  1175   1230       { "count",  1, 0, 0, countStep,    countFinalize  },
  1176   1231     };
  1177   1232     int i;
  1178   1233   
  1179   1234     for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
  1180         -    void *pArg = 0;
  1181         -    switch( aFuncs[i].argType ){
  1182         -      case 1: pArg = db; break;
  1183         -      case 2: pArg = (void *)(-1); break;
         1235  +    void *pArg;
         1236  +    u8 argType = aFuncs[i].argType;
         1237  +    if( argType==0xff ){
         1238  +      pArg = db;
         1239  +    }else{
         1240  +      pArg = (void*)(int)argType;
  1184   1241       }
  1185   1242       sqlite3CreateFunc(db, aFuncs[i].zName, aFuncs[i].nArg,
  1186   1243           aFuncs[i].eTextRep, pArg, aFuncs[i].xFunc, 0, 0);
  1187   1244       if( aFuncs[i].needCollSeq ){
  1188   1245         FuncDef *pFunc = sqlite3FindFunction(db, aFuncs[i].zName, 
  1189   1246             strlen(aFuncs[i].zName), aFuncs[i].nArg, aFuncs[i].eTextRep, 0);
  1190   1247         if( pFunc && aFuncs[i].needCollSeq ){
................................................................................
  1195   1252   #ifndef SQLITE_OMIT_ALTERTABLE
  1196   1253     sqlite3AlterFunctions(db);
  1197   1254   #endif
  1198   1255   #ifndef SQLITE_OMIT_PARSER
  1199   1256     sqlite3AttachFunctions(db);
  1200   1257   #endif
  1201   1258     for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
  1202         -    void *pArg = 0;
  1203         -    switch( aAggs[i].argType ){
  1204         -      case 1: pArg = db; break;
  1205         -      case 2: pArg = (void *)(-1); break;
  1206         -    }
         1259  +    void *pArg = (void*)(int)aAggs[i].argType;
  1207   1260       sqlite3CreateFunc(db, aAggs[i].zName, aAggs[i].nArg, SQLITE_UTF8, 
  1208   1261           pArg, 0, aAggs[i].xStep, aAggs[i].xFinalize);
  1209   1262       if( aAggs[i].needCollSeq ){
  1210   1263         FuncDef *pFunc = sqlite3FindFunction( db, aAggs[i].zName,
  1211   1264             strlen(aAggs[i].zName), aAggs[i].nArg, SQLITE_UTF8, 0);
  1212   1265         if( pFunc && aAggs[i].needCollSeq ){
  1213   1266           pFunc->needCollSeq = 1;

Changes to test/func.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing built-in functions.
    13     13   #
    14         -# $Id: func.test,v 1.58 2007/03/17 13:27:56 drh Exp $
           14  +# $Id: func.test,v 1.59 2007/03/17 17:52:42 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Create a table to work with.
    20     20   #
    21     21   do_test func-0.0 {
................................................................................
   436    436       DROP TABLE t4;
   437    437     }
   438    438   } {}
   439    439   
   440    440   # Test that the auxdata API for scalar functions works. This test uses
   441    441   # a special user-defined function only available in test builds,
   442    442   # test_auxdata(). Function test_auxdata() takes any number of arguments.
   443         -btree_breakpoint
   444    443   do_test func-13.1 {
   445    444     execsql {
   446    445       SELECT test_auxdata('hello world');
   447    446     }
   448    447   } {0}
   449    448   
   450    449   do_test func-13.2 {
................................................................................
   768    767   } {{This is the larger-main test string}}
   769    768   do_test func-21.8 {
   770    769     execsql {
   771    770       SELECT replace("aaaaaaa", "a", "0123456789");
   772    771     }
   773    772   } {0123456789012345678901234567890123456789012345678901234567890123456789}
   774    773   
   775         -
          774  +# Tests for the TRIM, LTRIM and RTRIM functions.
          775  +#
          776  +do_test func-22.1 {
          777  +  catchsql {SELECT trim(1,2,3)}
          778  +} {1 {wrong number of arguments to function trim()}}
          779  +do_test func-22.2 {
          780  +  catchsql {SELECT ltrim(1,2,3)}
          781  +} {1 {wrong number of arguments to function ltrim()}}
          782  +do_test func-22.3 {
          783  +  catchsql {SELECT rtrim(1,2,3)}
          784  +} {1 {wrong number of arguments to function rtrim()}}
          785  +do_test func-22.4 {
          786  +  execsql {SELECT trim('  hi  ');}
          787  +} {hi}
          788  +do_test func-22.5 {
          789  +  execsql {SELECT ltrim('  hi  ');}
          790  +} {{hi  }}
          791  +do_test func-22.6 {
          792  +  execsql {SELECT rtrim('  hi  ');}
          793  +} {{  hi}}
          794  +do_test func-22.7 {
          795  +  execsql {SELECT trim('  hi  ','xyz');}
          796  +} {{  hi  }}
          797  +do_test func-22.8 {
          798  +  execsql {SELECT ltrim('  hi  ','xyz');}
          799  +} {{  hi  }}
          800  +do_test func-22.9 {
          801  +  execsql {SELECT rtrim('  hi  ','xyz');}
          802  +} {{  hi  }}
          803  +do_test func-22.10 {
          804  +  execsql {SELECT trim('xyxzy  hi  zzzy','xyz');}
          805  +} {{  hi  }}
          806  +do_test func-22.11 {
          807  +  execsql {SELECT ltrim('xyxzy  hi  zzzy','xyz');}
          808  +} {{  hi  zzzy}}
          809  +do_test func-22.12 {
          810  +  execsql {SELECT rtrim('xyxzy  hi  zzzy','xyz');}
          811  +} {{xyxzy  hi  }}
          812  +do_test func-22.13 {
          813  +  execsql {SELECT trim('  hi  ','');}
          814  +} {{  hi  }}
          815  +do_test func-22.20 {
          816  +  execsql {SELECT typeof(trim(NULL));}
          817  +} {null}
          818  +do_test func-22.21 {
          819  +  execsql {SELECT typeof(trim(NULL,'xyz'));}
          820  +} {null}
          821  +do_test func-22.22 {
          822  +  execsql {SELECT typeof(trim('hello',NULL));}
          823  +} {null}
   776    824   
   777    825   finish_test