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: |
49ccae964f3a8ae5aab87f56503121e0 |
| 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
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