RE: [sqlite] Convert and isnumeric function
Dan, You are absolutely correct - I always get those two confused. I should have looked it up instead of relying on memory. Liberia - Nigeria - two letters different. My ECC must be faulty. It only catches single bit errors. :-) Mike > -Original Message- > From: Dan Kennedy [mailto:[EMAIL PROTECTED] > Sent: Monday, May 16, 2005 10:34 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Convert and isnumeric function > > > There's a former Nigeria dictator Charles Taylor that is even worse. > > Liberia! Not that I could tell one from the other :) > > > > > Yahoo! Mail > Stay connected, organized, and protected. Take the tour: > http://tour.mail.yahoo.com/mailtour.html > >
RE: [sqlite] Convert and isnumeric function
> There's a former Nigeria dictator Charles Taylor that is even > worse. Liberia! Not that I could tell one from the other :) Yahoo! Mail Stay connected, organized, and protected. Take the tour: http://tour.mail.yahoo.com/mailtour.html
RE: [sqlite] Convert and isnumeric function
Cory,
Thanks, I'll give it a try. Speed if of the utmost for me here,
because I am building an array of ID's in memory that satisfy a certain
condition and then using that array of ID's back into the database to add to
the array using the keys in the array as the condition for the next select.
I keep iterating like this until there are no more ID's to add to the list.
The array can get to be over 10,000 rows at up to 30 levels. I also track
the levels at which the ID was entered into the table. Obviously I eliminate
the ID's past each level that are already in the array. I currently am doing
this in MSSQL 2K and the CircleOfFriends Database alone (without the profile
data) is over 9GB and has over 94 million rows. It's not distributable in
this format, so I am using sqlite to ship the 289,000+ profiles and building
the lists on the fly in a web application. We ship a new profile every
single day to our clients and the circle of friends list needs to be rebuilt
every time they get a new list.
Basically what it is, is a database of bad guys and companies from
various world wide lists that each have a unique ID assigned to them. Part
of the database is a table of links to other bad guys that are in the list.
I am using this method to build a 'Circle Of Friends' of the bad guys. We
then rank each member of the Circle according to how many other bad guys
he/she is linked to and assign a severity based on the list or list that
they are on. The list currently contains about 289,000+ profiles of the
worlds top bad guys. By the way - Usama Bin Laden is in a circle that
contains about 9,000+ other bad guys/companies. There's a former Nigeria
dictator Charles Taylor that is even worse.
Mike
> -Original Message-
> From: Cory Nelson [mailto:[EMAIL PROTECTED]
> Sent: Sunday, May 15, 2005 1:36 PM
> To: sqlite-users@sqlite.org
> Cc: [EMAIL PROTECTED]
> Subject: Re: [sqlite] Convert and isnumeric function
>
> for a nice speed increase (no strlen and only one pointer
> dereference) try changing
>
> for (i = 0; i < strlen (z); i++) {
>
> to
>
> const char *iter;
> char ch;
> for(iter=z; (ch=*iter); ++iter) {
>
> and
>
> if (i == 0) {
>
> to
>
> if(iter==z) {
>
> and all the z[i] to ch.
>
> On 5/15/05, Michael Evenson <[EMAIL PROTECTED]> wrote:
> > This fixes the problem of negative numeric values. It does
> not however
> > handle locale specific numbers (like using , as decimal
> separator) or
> > for that matter, commas in the numbers. As I understand it, sqlite
> > does not handle locale specific numbers in general.
> >
> > Mike
> >
> >
> > /*
> > ** Implementation of the isnumeric() function */ static void
> > isnumericFunc(sqlite3_context *context, int argc, sqlite3_value
> > **argv)
> > {
> > int i;
> > int nResult = 1;
> >
> > assert( argc==1 );
> > switch( sqlite3_value_type(argv[0]) ){
> > case SQLITE_INTEGER: {
> > sqlite3_result_int(context, 1);
> > break;
> > }
> > case SQLITE_FLOAT: {
> > sqlite3_result_int(context, 1);
> > break;
> > }
> > case SQLITE_NULL: {
> > sqlite3_result_int(context, 0);
> > break;
> > }
> > case SQLITE_TEXT: {
> > int d = 0;
> > const char *z = sqlite3_value_text(argv[0]);
> > for (i = 0; i < strlen (z); i++) {
> > if (!isdigit (z[i])) {
> > /* the character is not a digit */
> > if (i == 0) {
> > /* allow - or + as the first character */
> > if ((z[i] != '-') && (z[i] != '+') && (z[i] != '.')) {
> > /* only +, - and . allowed as first non digit
> character */
> > nResult = 0;
> > if (z[i] == '.')
> > d++;
> > break;
> > }
> > }
> > else {
> > if ((d > 0) && (z[i] == '.')) {
> > /* only . allowed as non digit character here */
> > /* and only one of them in the string */
> > nResult = 0;
> > break;
> > }
> > else if (z[i] == '.')
> > d++;
> > }
> > }
> > }
> > sqlite3_result_int(context, nResult);
> > break;
> > }
> > default: {
> > sqlite3_result_int(context, 0);
> > break;
> > }
> > }
> > }
> &
Re: [sqlite] Convert and isnumeric function
for a nice speed increase (no strlen and only one pointer dereference)
try changing
for (i = 0; i < strlen (z); i++) {
to
const char *iter;
char ch;
for(iter=z; (ch=*iter); ++iter) {
and
if (i == 0) {
to
if(iter==z) {
and all the z[i] to ch.
On 5/15/05, Michael Evenson <[EMAIL PROTECTED]> wrote:
> This fixes the problem of negative numeric values. It does not however
> handle locale specific numbers (like using , as decimal separator) or for
> that matter, commas in the numbers. As I understand it, sqlite does not
> handle locale specific numbers in general.
>
> Mike
>
>
> /*
> ** Implementation of the isnumeric() function
> */
> static void isnumericFunc(sqlite3_context *context, int argc, sqlite3_value
> **argv)
> {
> int i;
> int nResult = 1;
>
> assert( argc==1 );
> switch( sqlite3_value_type(argv[0]) ){
> case SQLITE_INTEGER: {
> sqlite3_result_int(context, 1);
> break;
> }
> case SQLITE_FLOAT: {
> sqlite3_result_int(context, 1);
> break;
> }
> case SQLITE_NULL: {
> sqlite3_result_int(context, 0);
> break;
> }
> case SQLITE_TEXT: {
> int d = 0;
> const char *z = sqlite3_value_text(argv[0]);
> for (i = 0; i < strlen (z); i++) {
> if (!isdigit (z[i])) {
> /* the character is not a digit */
> if (i == 0) {
> /* allow - or + as the first character */
> if ((z[i] != '-') && (z[i] != '+') && (z[i] != '.')) {
> /* only +, - and . allowed as first non digit character */
> nResult = 0;
> if (z[i] == '.')
> d++;
> break;
> }
> }
> else {
> if ((d > 0) && (z[i] == '.')) {
> /* only . allowed as non digit character here */
> /* and only one of them in the string */
> nResult = 0;
> break;
> }
> else if (z[i] == '.')
> d++;
> }
> }
> }
> sqlite3_result_int(context, nResult);
> break;
> }
> default: {
> sqlite3_result_int(context, 0);
> break;
> }
> }
> }
>
> > -Original Message-
> > From: Wolfgang Rohdewald [mailto:[EMAIL PROTECTED]
> > Sent: Saturday, May 14, 2005 8:39 AM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Convert and isnumeric function
> >
> > On Samstag 14 Mai 2005 00:31, Michael Evenson wrote:
> > > case SQLITE_TEXT: {
> > > const char *z = sqlite3_value_text(argv[0]);
> > > for (i = 0; i < strlen (z); i++) {
> > > if (!isdigit (z[i])) {
> > > nResult = 0;
> > > break;
> > > }
> >
> > this should return FALSE for -5, 1123.456.
> > In some locales, that might be written as 1'123,456 or
> > 1,123.456 or whatever.
> >
> > strtol()
> >
> > seems to what you need, see man strol()
> >
> > --
> > Wolfgang
> >
>
>
--
Cory Nelson
http://www.int64.org
RE: [sqlite] Convert and isnumeric function
This fixes the problem of negative numeric values. It does not however
handle locale specific numbers (like using , as decimal separator) or for
that matter, commas in the numbers. As I understand it, sqlite does not
handle locale specific numbers in general.
Mike
/*
** Implementation of the isnumeric() function
*/
static void isnumericFunc(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
int i;
int nResult = 1;
assert( argc==1 );
switch( sqlite3_value_type(argv[0]) ){
case SQLITE_INTEGER: {
sqlite3_result_int(context, 1);
break;
}
case SQLITE_FLOAT: {
sqlite3_result_int(context, 1);
break;
}
case SQLITE_NULL: {
sqlite3_result_int(context, 0);
break;
}
case SQLITE_TEXT: {
int d = 0;
const char *z = sqlite3_value_text(argv[0]);
for (i = 0; i < strlen (z); i++) {
if (!isdigit (z[i])) {
/* the character is not a digit */
if (i == 0) {
/* allow - or + as the first character */
if ((z[i] != '-') && (z[i] != '+') && (z[i] != '.')) {
/* only +, - and . allowed as first non digit character */
nResult = 0;
if (z[i] == '.')
d++;
break;
}
}
else {
if ((d > 0) && (z[i] == '.')) {
/* only . allowed as non digit character here */
/* and only one of them in the string */
nResult = 0;
break;
}
else if (z[i] == '.')
d++;
}
}
}
sqlite3_result_int(context, nResult);
break;
}
default: {
sqlite3_result_int(context, 0);
break;
}
}
}
> -Original Message-
> From: Wolfgang Rohdewald [mailto:[EMAIL PROTECTED]
> Sent: Saturday, May 14, 2005 8:39 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Convert and isnumeric function
>
> On Samstag 14 Mai 2005 00:31, Michael Evenson wrote:
> > case SQLITE_TEXT: {
> > const char *z = sqlite3_value_text(argv[0]);
> > for (i = 0; i < strlen (z); i++) {
> > if (!isdigit (z[i])) {
> > nResult = 0;
> > break;
> > }
>
> this should return FALSE for -5, 1123.456.
> In some locales, that might be written as 1'123,456 or
> 1,123.456 or whatever.
>
> strtol()
>
> seems to what you need, see man strol()
>
> --
> Wolfgang
>
RE: [sqlite] Convert and isnumeric function
You are absolutely right. I guess in my haste to get it working for positive
integers only, I put the shrink wrapper on to soon.
Mike
> -Original Message-
> From: Wolfgang Rohdewald [mailto:[EMAIL PROTECTED]
> Sent: Saturday, May 14, 2005 8:39 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Convert and isnumeric function
>
> On Samstag 14 Mai 2005 00:31, Michael Evenson wrote:
> > case SQLITE_TEXT: {
> > const char *z = sqlite3_value_text(argv[0]);
> > for (i = 0; i < strlen (z); i++) {
> > if (!isdigit (z[i])) {
> > nResult = 0;
> > break;
> > }
>
> this should return FALSE for -5, 1123.456.
> In some locales, that might be written as 1'123,456 or
> 1,123.456 or whatever.
>
> strtol()
>
> seems to what you need, see man strol()
>
> --
> Wolfgang
>
Re: [sqlite] Convert and isnumeric function
On Samstag 14 Mai 2005 00:31, Michael Evenson wrote:
> case SQLITE_TEXT: {
> const char *z = sqlite3_value_text(argv[0]);
> for (i = 0; i < strlen (z); i++) {
> if (!isdigit (z[i])) {
> nResult = 0;
> break;
> }
this should return FALSE for -5, 1123.456.
In some locales, that might be written as 1'123,456 or 1,123.456
or whatever.
strtol()
seems to what you need, see man strol()
--
Wolfgang
RE: [sqlite] Convert and isnumeric function
Jay, I kindly beg to differ. I have a situation where a column can contain either a name (like Joe Blow) or a numeric link to another name (like 12345). If the column contains a numeric value then the link is followed otherwise just the name is used. I would normally just handle it in code, but I need to create a view that does a union with another table that only allows numeric values in the same column. Hence I need the isnumeric to eliminate the rows where the name is not numeric. I don't want them in the view. I can probably get away without the convert funvtions. I'm going to try that now. I compiled the isnumeric function into the sqlite3 code and it works great. Here's the view create view CombinedLinksTo as select nUID, nLinkedTo, cFullName, 'I' cType from WorldCheckData_LinksTo union select nUID, cCompany nLinkedTo, cFullName, 'E' cType from WorldCheckData_Companies where isNumeric (cCompany) = 1 and cCompany != '.' Mike > -Original Message- > From: Jay Sprenkle [mailto:[EMAIL PROTECTED] > Sent: Friday, May 13, 2005 3:47 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Convert and isnumeric function > > > All or any, > > > > Has anyone implemented these as built in functions > in sqlite > > 3? If not - I was thinking of doing it and submitting to the > > sqlite.org web site for submission. > > The api converts data types for you. Isnumeric might be > useful but since the database isn't strongly typed it > probably wouldn't get used much. >
RE: [sqlite] Convert and isnumeric function
Here is the isnumeric function. Don't forget to add the line to the
sqlite3RegisterBuiltinFunctions function.
{ "isnumeric", 1, 0, SQLITE_UTF8,0, isnumericFunc},
I put it right after the abs function declartation
/*
--*/
/*
** Implementation of the isnumeric() function
*/
static void isnumericFunc(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
int i;
int nResult = 1;
assert( argc==1 );
switch( sqlite3_value_type(argv[0]) ){
case SQLITE_INTEGER: {
sqlite3_result_int(context, 1);
break;
}
case SQLITE_NULL: {
sqlite3_result_int(context, 0);
break;
}
case SQLITE_TEXT: {
const char *z = sqlite3_value_text(argv[0]);
for (i = 0; i < strlen (z); i++) {
if (!isdigit (z[i])) {
nResult = 0;
break;
}
}
sqlite3_result_int(context, nResult);
break;
}
default: {
sqlite3_result_int(context, 0);
break;
}
}
}
/*
--*/
Michael Evenson
P.s.
It sure would be nice to have this in the next release
Re: [sqlite] Convert and isnumeric function
> All or any, > > Has anyone implemented these as built in functions in sqlite 3? If > not - I was thinking of doing it and submitting to the sqlite.org web site > for submission. The api converts data types for you. Isnumeric might be useful but since the database isn't strongly typed it probably wouldn't get used much.
