Find non-numeric character in text field

[RB Smissaert]

I would like to do a where on a text field and check if the values have
non-numeric characters,
which is in this case is anything other than 1,2,3,4,5,6,7,8,9,0 or a space
character.
Is this possible without using a UDF or a very long OR construction?

RBS


_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

RB Smissaert wrote:
> I would like to do a where on a text field and check if the values have
> non-numeric characters,
> which is in this case is anything other than 1,2,3,4,5,6,7,8,9,0 or a space
> character.
> Is this possible without using a UDF or a very long OR construction?

You can check to see if the string is zero length after calling trim on it.
 For example trim(value, "01234567890 ") will remove all those characters.

Also you do know that those aren't the only way numbers are written?  See
the pdf at the bottom of http://www.unicode.org/notes/tn21/ which shows the
Tamil number system and uses different digits.  BTW there are over 66
million Tamil speakers, it is an official language of several countries and
has a long history.

I'll let you off the hook for not considering Roman Numerals though :-)

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkryBL8ACgkQmOOfHg372QShCQCfT80ioArm0O5RFinQew0wnBr7
hfcAniv7GeCmU8RIFM/5VjbO8+MklUp0
=t+LY
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Jean-Christophe Deschamps]

>I would like to do a where on a text field and check if the values have
>non-numeric characters,
>which is in this case is anything other than 1,2,3,4,5,6,7,8,9,0 or a
>space
>character.
>Is this possible without using a UDF or a very long OR construction?

select * from mytable where mycol glob '*[^0-9 ]*';




_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Tomek Jerzykowski]

Sqlite 3.6.20, sqliteint.h:

/*
** The datatype ynVar is a signed integer, either 16-bit or 32-bit.
(...)
*/
#if SQLITE_MAX_VARIABLE_NUMBER<=32767
typedef i64 ynVar;
#else
typedef int ynVar;
#endif

Comment contradicts actual code. It seems to me that code is wrong.

Tomek


_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Dan Kennedy-4]

On Nov 5, 2009, at 5:04 PM, Tomek Jerzykowski wrote:

> Sqlite 3.6.20, sqliteint.h:
>
> /*
> ** The datatype ynVar is a signed integer, either 16-bit or 32-bit.
> (...)
> */
> #if SQLITE_MAX_VARIABLE_NUMBER<=32767
> typedef i64 ynVar;
> #else
> typedef int ynVar;
> #endif
>
> Comment contradicts actual code. It seems to me that code is wrong.

As you suspect, it was a typo. Is now fixed here:

   http://www.sqlite.org/src/vinfo/f1c09acaca3e205acf5b077c9b2d0fe35f035c1e

Dan.

_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users