/ Check-in [49ccae96]
Login

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

Overview
Comment:Implementation of the INSTR() SQL function, as found in SQL Server, MySQL, and Oracle.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | instr
Files: files | file ages | folders
SHA1: 49ccae964f3a8ae5aab87f56503121e09424545f
User & Date: drh 2012-10-25 03:07:29
Context
2012-11-05
13:51
Add the INSTR() SQL function. check-in: a4c181cb user: drh tags: trunk
2012-10-25
03:07
Implementation of the INSTR() SQL function, as found in SQL Server, MySQL, and Oracle. Closed-Leaf check-in: 49ccae96 user: drh tags: instr
2012-10-19
02:10
Make sure substructure elements have proper alignment in the ICU tokenizers of FTS2 and FTS3. check-in: aaa2d9b0 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/func.c.

   163    163         double rVal = sqlite3_value_double(argv[0]);
   164    164         if( rVal<0 ) rVal = -rVal;
   165    165         sqlite3_result_double(context, rVal);
   166    166         break;
   167    167       }
   168    168     }
   169    169   }
          170  +
          171  +/*
          172  +** Implementation of the instr() function.
          173  +**
          174  +** instr(haystack,needle) finds the first occurrence of needle
          175  +** in haystack and returns the number of previous characters plus 1,
          176  +** or 0 if needle does not occur within haystack.
          177  +**
          178  +** If both haystack and needle are BLOBs, then the result is one more than
          179  +** the number of bytes in haystack prior to the first occurrence of needle,
          180  +** or 0 if needle never occurs in haystack.
          181  +*/
          182  +static void instrFunc(
          183  +  sqlite3_context *context,
          184  +  int argc,
          185  +  sqlite3_value **argv
          186  +){
          187  +  const unsigned char *zHaystack;
          188  +  const unsigned char *zNeedle;
          189  +  int nHaystack;
          190  +  int nNeedle;
          191  +  int typeHaystack, typeNeedle;
          192  +  int N = 1;
          193  +  int isText;
          194  +
          195  +  typeHaystack = sqlite3_value_type(argv[0]);
          196  +  typeNeedle = sqlite3_value_type(argv[1]);
          197  +  if( typeHaystack==SQLITE_NULL || typeNeedle==SQLITE_NULL ) return;
          198  +  nHaystack = sqlite3_value_bytes(argv[0]);
          199  +  nNeedle = sqlite3_value_bytes(argv[1]);
          200  +  if( typeHaystack==SQLITE_BLOB && typeNeedle==SQLITE_BLOB ){
          201  +    zHaystack = sqlite3_value_blob(argv[0]);
          202  +    zNeedle = sqlite3_value_blob(argv[1]);
          203  +    isText = 0;
          204  +  }else{
          205  +    zHaystack = sqlite3_value_text(argv[0]);
          206  +    zNeedle = sqlite3_value_text(argv[1]);
          207  +    isText = 1;
          208  +  }
          209  +  while( nNeedle<=nHaystack && memcmp(zHaystack, zNeedle, nNeedle)!=0 ){
          210  +    N++;
          211  +    do{
          212  +      nHaystack--;
          213  +      zHaystack++;
          214  +    }while( isText && (zHaystack[0]&0xc0)==0x80 );
          215  +  }
          216  +  if( nNeedle>nHaystack ) N = 0;
          217  +  sqlite3_result_int(context, N);
          218  +}
   170    219   
   171    220   /*
   172    221   ** Implementation of the substr() function.
   173    222   **
   174    223   ** substr(x,p1,p2)  returns p2 characters of x[] beginning with p1.
   175    224   ** p1 is 1-indexed.  So substr(x,1,1) returns the first character
   176    225   ** of x.  If x is text, then we actually count UTF-8 characters.
................................................................................
  1532   1581       FUNCTION(min,                0, 0, 1, 0                ),
  1533   1582       AGGREGATE(min,               1, 0, 1, minmaxStep,      minMaxFinalize ),
  1534   1583       FUNCTION(max,               -1, 1, 1, minmaxFunc       ),
  1535   1584       FUNCTION(max,                0, 1, 1, 0                ),
  1536   1585       AGGREGATE(max,               1, 1, 1, minmaxStep,      minMaxFinalize ),
  1537   1586       FUNCTION2(typeof,            1, 0, 0, typeofFunc,  SQLITE_FUNC_TYPEOF),
  1538   1587       FUNCTION2(length,            1, 0, 0, lengthFunc,  SQLITE_FUNC_LENGTH),
         1588  +    FUNCTION(instr,              2, 0, 0, instrFunc        ),
  1539   1589       FUNCTION(substr,             2, 0, 0, substrFunc       ),
  1540   1590       FUNCTION(substr,             3, 0, 0, substrFunc       ),
  1541   1591       FUNCTION(abs,                1, 0, 0, absFunc          ),
  1542   1592   #ifndef SQLITE_OMIT_FLOATING_POINT
  1543   1593       FUNCTION(round,              1, 0, 0, roundFunc        ),
  1544   1594       FUNCTION(round,              2, 0, 0, roundFunc        ),
  1545   1595   #endif

Added test/instr.test.

            1  +# 2012 October 24
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing the built-in INSTR() functions.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +
           18  +# Create a table to work with.
           19  +#
           20  +do_test instr-1.1 {
           21  +  db eval {SELECT instr('abcdefg','a');}
           22  +} {1}
           23  +do_test instr-1.2 {
           24  +  db eval {SELECT instr('abcdefg','b');}
           25  +} {2}
           26  +do_test instr-1.3 {
           27  +  db eval {SELECT instr('abcdefg','c');}
           28  +} {3}
           29  +do_test instr-1.4 {
           30  +  db eval {SELECT instr('abcdefg','d');}
           31  +} {4}
           32  +do_test instr-1.5 {
           33  +  db eval {SELECT instr('abcdefg','e');}
           34  +} {5}
           35  +do_test instr-1.6 {
           36  +  db eval {SELECT instr('abcdefg','f');}
           37  +} {6}
           38  +do_test instr-1.7 {
           39  +  db eval {SELECT instr('abcdefg','g');}
           40  +} {7}
           41  +do_test instr-1.8 {
           42  +  db eval {SELECT instr('abcdefg','h');}
           43  +} {0}
           44  +do_test instr-1.9 {
           45  +  db eval {SELECT instr('abcdefg','abcdefg');}
           46  +} {1}
           47  +do_test instr-1.10 {
           48  +  db eval {SELECT instr('abcdefg','abcdefgh');}
           49  +} {0}
           50  +do_test instr-1.11 {
           51  +  db eval {SELECT instr('abcdefg','bcdefg');}
           52  +} {2}
           53  +do_test instr-1.12 {
           54  +  db eval {SELECT instr('abcdefg','bcdefgh');}
           55  +} {0}
           56  +do_test instr-1.13 {
           57  +  db eval {SELECT instr('abcdefg','cdefg');}
           58  +} {3}
           59  +do_test instr-1.14 {
           60  +  db eval {SELECT instr('abcdefg','cdefgh');}
           61  +} {0}
           62  +do_test instr-1.15 {
           63  +  db eval {SELECT instr('abcdefg','defg');}
           64  +} {4}
           65  +do_test instr-1.16 {
           66  +  db eval {SELECT instr('abcdefg','defgh');}
           67  +} {0}
           68  +do_test instr-1.17 {
           69  +  db eval {SELECT instr('abcdefg','efg');}
           70  +} {5}
           71  +do_test instr-1.18 {
           72  +  db eval {SELECT instr('abcdefg','efgh');}
           73  +} {0}
           74  +do_test instr-1.19 {
           75  +  db eval {SELECT instr('abcdefg','fg');}
           76  +} {6}
           77  +do_test instr-1.20 {
           78  +  db eval {SELECT instr('abcdefg','fgh');}
           79  +} {0}
           80  +do_test instr-1.21 {
           81  +  db eval {SELECT coalesce(instr('abcdefg',NULL),'nil');}
           82  +} {nil}
           83  +do_test instr-1.22 {
           84  +  db eval {SELECT coalesce(instr(NULL,'x'),'nil');}
           85  +} {nil}
           86  +do_test instr-1.23 {
           87  +  db eval {SELECT instr(12345,34);}
           88  +} {3}
           89  +do_test instr-1.24 {
           90  +  db eval {SELECT instr(123456.78,34);}
           91  +} {3}
           92  +do_test instr-1.25 {
           93  +  db eval {SELECT instr(123456.78,x'3334');}
           94  +} {3}
           95  +do_test instr-1.26 {
           96  +  db eval {SELECT instr('äbcdefg','efg');}
           97  +} {5}
           98  +do_test instr-1.27 {
           99  +  db eval {SELECT instr('€xyzzy','xyz');}
          100  +} {2}
          101  +do_test instr-1.28 {
          102  +  db eval {SELECT instr('abc€xyzzy','xyz');}
          103  +} {5}
          104  +do_test instr-1.29 {
          105  +  db eval {SELECT instr('abc€xyzzy','€xyz');}
          106  +} {4}
          107  +do_test instr-1.30 {
          108  +  db eval {SELECT instr('abc€xyzzy','c€xyz');}
          109  +} {3}
          110  +do_test instr-1.31 {
          111  +  db eval {SELECT instr(x'0102030405',x'01');}
          112  +} {1}
          113  +do_test instr-1.32 {
          114  +  db eval {SELECT instr(x'0102030405',x'02');}
          115  +} {2}
          116  +do_test instr-1.33 {
          117  +  db eval {SELECT instr(x'0102030405',x'03');}
          118  +} {3}
          119  +do_test instr-1.34 {
          120  +  db eval {SELECT instr(x'0102030405',x'04');}
          121  +} {4}
          122  +do_test instr-1.35 {
          123  +  db eval {SELECT instr(x'0102030405',x'05');}
          124  +} {5}
          125  +do_test instr-1.36 {
          126  +  db eval {SELECT instr(x'0102030405',x'06');}
          127  +} {0}
          128  +do_test instr-1.37 {
          129  +  db eval {SELECT instr(x'0102030405',x'0102030405');}
          130  +} {1}
          131  +do_test instr-1.38 {
          132  +  db eval {SELECT instr(x'0102030405',x'02030405');}
          133  +} {2}
          134  +do_test instr-1.39 {
          135  +  db eval {SELECT instr(x'0102030405',x'030405');}
          136  +} {3}
          137  +do_test instr-1.40 {
          138  +  db eval {SELECT instr(x'0102030405',x'0405');}
          139  +} {4}
          140  +do_test instr-1.41 {
          141  +  db eval {SELECT instr(x'0102030405',x'0506');}
          142  +} {0}
          143  +do_test instr-1.42 {
          144  +  db eval {SELECT instr(x'0102030405',x'');}
          145  +} {1}
          146  +do_test instr-1.43 {
          147  +  db eval {SELECT instr(x'',x'');}
          148  +} {1}
          149  +do_test instr-1.44 {
          150  +  db eval {SELECT instr('','');}
          151  +} {1}
          152  +do_test instr-1.45 {
          153  +  db eval {SELECT instr('abcdefg','');}
          154  +} {1}
          155  +unset -nocomplain longstr
          156  +set longstr abcdefghijklmonpqrstuvwxyz
          157  +append longstr $longstr
          158  +append longstr $longstr
          159  +append longstr $longstr
          160  +append longstr $longstr
          161  +append longstr $longstr
          162  +append longstr $longstr
          163  +append longstr $longstr
          164  +append longstr $longstr
          165  +append longstr $longstr
          166  +append longstr $longstr
          167  +append longstr $longstr
          168  +append longstr $longstr
          169  +# puts [string length $longstr]
          170  +append longstr Xabcde
          171  +do_test instr-1.46 {
          172  +  db eval {SELECT instr($longstr,'X');}
          173  +} {106497}
          174  +do_test instr-1.47 {
          175  +  db eval {SELECT instr($longstr,'Y');}
          176  +} {0}
          177  +do_test instr-1.48 {
          178  +  db eval {SELECT instr($longstr,'Xa');}
          179  +} {106497}
          180  +do_test instr-1.49 {
          181  +  db eval {SELECT instr($longstr,'zXa');}
          182  +} {106496}
          183  +set longstr [string map {a ä} $longstr]
          184  +do_test instr-1.50 {
          185  +  db eval {SELECT instr($longstr,'X');}
          186  +} {106497}
          187  +do_test instr-1.51 {
          188  +  db eval {SELECT instr($longstr,'Y');}
          189  +} {0}
          190  +do_test instr-1.52 {
          191  +  db eval {SELECT instr($longstr,'Xä');}
          192  +} {106497}
          193  +do_test instr-1.53 {
          194  +  db eval {SELECT instr($longstr,'zXä');}
          195  +} {106496}
          196  +do_test instr-1.54 {
          197  +  db eval {SELECT instr(x'78c3a4e282ac79','x');}
          198  +} {1}
          199  +do_test instr-1.55 {
          200  +  db eval {SELECT instr(x'78c3a4e282ac79','y');}
          201  +} {4}
          202  +do_test instr-1.56 {
          203  +  db eval {SELECT instr(x'78c3a4e282ac79',x'79');}
          204  +} {7}
          205  +do_test instr-1.57 {
          206  +  db eval {SELECT instr('xä€y',x'79');}
          207  +} {4}
          208  +
          209  +
          210  +finish_test