Searching for a LIKE '[0-9]%' equivalence in sqlite

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Searching for a LIKE '[0-9]%' equivalence in sqlite

Flakheart
To to get columns that begin with a number range or any number, I would use in SQL server:

SELECT * FROM mytable WHERE myfield LIKE '[0-9]%'

This obviously doesn't work in sqlite and I have been searching for an equivalence.
Reply | Threaded
Open this post in threaded view
|

Re: Searching for a LIKE '[0-9]%' equivalence in sqlite

Jean-Christophe Deschamps

>To to get columns that begin with a number range or any number, I
>would use
>in SQL server:
>
>SELECT * FROM mytable WHERE myfield LIKE '[0-9]%'
>
>This obviously doesn't work in sqlite and I have been searching for an
>equivalence.

Try this:

SELECT * FROM mytable WHERE myfield glob '[0-9]*'



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Searching for a LIKE '[0-9]%' equivalence in sqlite

Jay Kreibich
In reply to this post by Flakheart
On Mon, Feb 01, 2010 at 01:49:59PM -0800, flakpit scratched on the wall:
>
> To to get columns that begin with a number range or any number, I would use
> in SQL server:
>
> SELECT * FROM mytable WHERE myfield LIKE '[0-9]%'

  Use "...WHERE myfield GLOB '[0-9]*' "


   -j


--
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Searching for a LIKE '[0-9]%' equivalence in sqlite

Flakheart
In reply to this post by Flakheart

flakpit wrote
To to get columns that begin with a number range or any number, I would use in SQL server:

SELECT * FROM mytable WHERE myfield LIKE '[0-9]%'

This obviously doesn't work in sqlite and I have been searching for an equivalence.
Thank you for your kind assistance folks, brilliant!
Reply | Threaded
Open this post in threaded view
|

Re: Searching for a LIKE '[0-9]%' equivalence in sqlite

Simon Slavin-3
In reply to this post by Flakheart

On 1 Feb 2010, at 9:49pm, flakpit wrote:

> To to get columns that begin with a number range or any number, I would use
> in SQL server:
>
> SELECT * FROM mytable WHERE myfield LIKE '[0-9]%'
>
> This obviously doesn't work in sqlite and I have been searching for an
> equivalence.

SELECT * FROM mytable WHERE myfield >= '0' AND myfield < ':'

If you have an index on 'myfield' this will be very fast.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Searching for a LIKE '[0-9]%' equivalence in sqlite

oliver1804
In reply to this post by Jean-Christophe Deschamps
Am Montag, den 01.02.2010, 22:55 +0100 schrieb Jean-Christophe
Deschamps:

[...]
>
> Try this:
>
> SELECT * FROM mytable WHERE myfield glob '[0-9]*'
>


thank you for opening my eyes - it seems that I never read
http://www.sqlite.org/lang_expr.html#glob with total awareness.

"The GLOB operator [...] uses the Unix file globbing syntax for its
wildcards."

But the reason might be that I usually don't use Linux for shell
operations because in the office I've to use WinXP. Maybe someone could
replenish the documentation with a few examples for the disadvantaged MS
users?

Oliver

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Searching for a LIKE '[0-9]%' equivalence in sqlite

Júlio César Ködel
uses the Unix file globbing *** syntax ***

It is the SYNTAX that it uses, not the UNIX FILE GLOBBING =)

On 2/1/10, Oliver Peters <[hidden email]> wrote:

> Am Montag, den 01.02.2010, 22:55 +0100 schrieb Jean-Christophe
> Deschamps:
>
> [...]
>>
>> Try this:
>>
>> SELECT * FROM mytable WHERE myfield glob '[0-9]*'
>>
>
>
> thank you for opening my eyes - it seems that I never read
> http://www.sqlite.org/lang_expr.html#glob with total awareness.
>
> "The GLOB operator [...] uses the Unix file globbing syntax for its
> wildcards."
>
> But the reason might be that I usually don't use Linux for shell
> operations because in the office I've to use WinXP. Maybe someone could
> replenish the documentation with a few examples for the disadvantaged MS
> users?
>
> Oliver
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


--
[]
Júlio César Ködel G.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Searching for a LIKE '[0-9]%' equivalence in sqlite

Jay Kreibich
In reply to this post by oliver1804
On Tue, Feb 02, 2010 at 12:55:34AM +0100, Oliver Peters scratched on the wall:

> Am Montag, den 01.02.2010, 22:55 +0100 schrieb Jean-Christophe
> Deschamps:
>
> [...]
> >
> > Try this:
> >
> > SELECT * FROM mytable WHERE myfield glob '[0-9]*'
>
> thank you for opening my eyes - it seems that I never read
> http://www.sqlite.org/lang_expr.html#glob with total awareness.
>
> "The GLOB operator [...] uses the Unix file globbing syntax for its
> wildcards."
>
> But the reason might be that I usually don't use Linux for shell
> operations because in the office I've to use WinXP. Maybe someone could
> replenish the documentation with a few examples for the disadvantaged MS
> users?

  From the source code...   Note that the glob expression is the "first"
  string because the glob() SQL function reverses the order of the
  parameters.


/*
** Compare two UTF-8 strings for equality where the first string can
** potentially be a "glob" expression.  Return true (1) if they
** are the same and false (0) if they are different.
**
** Globbing rules:
**
**      '*'       Matches any sequence of zero or more characters.
**
**      '?'       Matches exactly one character.
**
**     [...]      Matches one character from the enclosed list of
**                characters.
**
**     [^...]     Matches one character not in the enclosed list.
**
** With the [...] and [^...] matching, a ']' character can be included
** in the list by making it the first character after '[' or '^'.  A
** range of characters can be specified using '-'.  Example:
** "[a-z]" matches any single lower-case letter.  To match a '-', make
** it the last character in the list.
**
** This routine is usually quick, but can be N**2 in the worst case.
**
** Hints: to match '*' or '?', put them in "[]".  Like this:
**
**         abc[*]xyz        Matches "abc*xyz" only
*/




--
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users