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: |
6fe13eeade4fc7099fbda1e652064092 |
| 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
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