Overview
| SHA1 Hash: | 49ccae964f3a8ae5aab87f56503121e09424545f |
|---|---|
| Date: | 2012-10-25 03:07:29 |
| User: | drh |
| Comment: | Implementation of the INSTR() SQL function, as found in SQL Server, MySQL, and Oracle. |
Tags And Properties
- branch=instr propagates to descendants
- closed added by [097cd3d51b] on 2012-11-05 13:52:16
- sym-instr propagates to descendants
- sym-trunk cancelled
Changes
Changes to src/func.c
163 double rVal = sqlite3_value_double(argv[0]); 163 double rVal = sqlite3_value_double(argv[0]); 164 if( rVal<0 ) rVal = -rVal; 164 if( rVal<0 ) rVal = -rVal; 165 sqlite3_result_double(context, rVal); 165 sqlite3_result_double(context, rVal); 166 break; 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 ** Implementation of the substr() function. 221 ** Implementation of the substr() function. 173 ** 222 ** 174 ** substr(x,p1,p2) returns p2 characters of x[] beginning with p1. 223 ** substr(x,p1,p2) returns p2 characters of x[] beginning with p1. 175 ** p1 is 1-indexed. So substr(x,1,1) returns the first character 224 ** p1 is 1-indexed. So substr(x,1,1) returns the first character 176 ** of x. If x is text, then we actually count UTF-8 characters. 225 ** of x. If x is text, then we actually count UTF-8 characters. ................................................................................................................................................................................ 1532 FUNCTION(min, 0, 0, 1, 0 ), 1581 FUNCTION(min, 0, 0, 1, 0 ), 1533 AGGREGATE(min, 1, 0, 1, minmaxStep, minMaxFinalize ), 1582 AGGREGATE(min, 1, 0, 1, minmaxStep, minMaxFinalize ), 1534 FUNCTION(max, -1, 1, 1, minmaxFunc ), 1583 FUNCTION(max, -1, 1, 1, minmaxFunc ), 1535 FUNCTION(max, 0, 1, 1, 0 ), 1584 FUNCTION(max, 0, 1, 1, 0 ), 1536 AGGREGATE(max, 1, 1, 1, minmaxStep, minMaxFinalize ), 1585 AGGREGATE(max, 1, 1, 1, minmaxStep, minMaxFinalize ), 1537 FUNCTION2(typeof, 1, 0, 0, typeofFunc, SQLITE_FUNC_TYPEOF), 1586 FUNCTION2(typeof, 1, 0, 0, typeofFunc, SQLITE_FUNC_TYPEOF), 1538 FUNCTION2(length, 1, 0, 0, lengthFunc, SQLITE_FUNC_LENGTH), 1587 FUNCTION2(length, 1, 0, 0, lengthFunc, SQLITE_FUNC_LENGTH), > 1588 FUNCTION(instr, 2, 0, 0, instrFunc ), 1539 FUNCTION(substr, 2, 0, 0, substrFunc ), 1589 FUNCTION(substr, 2, 0, 0, substrFunc ), 1540 FUNCTION(substr, 3, 0, 0, substrFunc ), 1590 FUNCTION(substr, 3, 0, 0, substrFunc ), 1541 FUNCTION(abs, 1, 0, 0, absFunc ), 1591 FUNCTION(abs, 1, 0, 0, absFunc ), 1542 #ifndef SQLITE_OMIT_FLOATING_POINT 1592 #ifndef SQLITE_OMIT_FLOATING_POINT 1543 FUNCTION(round, 1, 0, 0, roundFunc ), 1593 FUNCTION(round, 1, 0, 0, roundFunc ), 1544 FUNCTION(round, 2, 0, 0, roundFunc ), 1594 FUNCTION(round, 2, 0, 0, roundFunc ), 1545 #endif 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