I scanned through the bug list on the website and didn't see one that was
related to this, so please forgive me if this has already been answered.
We have created a function that takes a string and returns a substring that is
split on a given character (I'll provide the source below), called splitstr.
In sqlite 3.0.8 we used this function and found that comparing the output to a
given string would use the case-sensitivity of the given string. Meaning that
if we used "select * from foo where splitstr(foo.col1, '@', 1 ) = 'foo';" and
col1 of table foo was defined as "text collate nocase" then the comparisons
would be case insensitive. This was the behavior in 3.0.8. It is no longer
the behavior in 3.6.23.
Is this working as designed and the 3.0.8 behavior was incorrect? Or is this a
valid bug?
To reproduce this I create the following table:
> create table foo( key integer primary key, value text collate nocase );
> insert into foo VALUES( NULL, 'f...@bar' );
> insert into foo VALUES( NULL, 'f...@bar' );
> insert into foo VALUES( NULL, 'f...@bar' );
> insert into foo VALUES( NULL, 'f...@bar' );
> select * from foo where splitstr( value, '@', 1 ) = 'foo';
4|f...@bar
However, in 3.0.8 I would get all records.
Splitstr() is a very simple function:
static void splitstrFunc( sqlite3_context *context, int argc, sqlite3_value
**argv ){ const char *z = NULL; char *temp = NULL; const char *p1 =
NULL; int p2 = 0; assert( argc==3 ); z = sqlite3_value_text(argv[0]);
if( z==0 ) return; p1 = sqlite3_value_text(argv[1]); if( p1 == 0 )
return; p2 = sqlite3_value_int(argv[2]); if( (temp = strchr( z, p1[0] )) ==
NULL ) return; *temp = '\0'; temp++; if( p2 == 2 ) z = temp; else
if( p2 != 1 ) /* must specify either first or second */ return;
sqlite3_result_text(context, z, -1, SQLITE_TRANSIENT); }
And is added to the sqlite3RegisterGlobalFunctions() aBuiltinFunc[] array as:
(FUNCTION(splitstr, 3, 0, 0, splitstrFunc ),
Thanks for your help
Jeff Webb
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
