RE: [sqlite] Using LIKE to check the first digits?
Regarding: >>Indices won't help with LIKE unless the column as a NOCASE collation. Use GLOB instead: >> ... WHERE number GLOB '1234*'; >>Note that "*" is the wildcard character with GLOB, not "%" >>as in LIKE. The above will use an index on the number column if it is available. === Thus, Dr. Hipp answers that age-old question, "What's not to like?" This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Using LIKE to check the first digits?
If you need LIKE 'abc%' and for it to not be case insensitive, then LIKE is preferred. Also, LIKE is standard SQL so if you're writing SQL that has to run across different vendors, you'd use LIKE. Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: P Kishor [mailto:[EMAIL PROTECTED] Sent: Thursday, January 31, 2008 9:28 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Using LIKE to check the first digits? if GLOB is so good, I am curious what are the instances in which one would prefer col LIKE '123%' over col GLOB '123*' - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using LIKE to check the first digits?
On 1/31/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Gilles <[EMAIL PROTECTED]> wrote: > > At 15:40 31/01/2008 -0800, James Dennett wrote: > > > > WHERE col LIKE '123%' or WHERE substr(col,1, 3) = '123' > > > > > >The optimizer has a decent chance of using an index for LIKE '123%' but > > >I'd be surprised (and impressed) if it looks inside function calls suchas > > >substr for opportunities to use indexes. > > > > Thanks guys. I'll remember to index the column, for higher performance. > > > > Indices won't help with LIKE unless the column as a NOCASE > collation. Use GLOB instead: > >... WHERE number GLOB '1234*'; > > Note that "*" is the wildcard character with GLOB, not "%" > as in LIKE. The above will use an index on the number column > if it is available. if GLOB is so good, I am curious what are the instances in which one would prefer col LIKE '123%' over col GLOB '123*' - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using LIKE to check the first digits?
At 02:01 01/02/2008 +, [EMAIL PROTECTED] wrote: Indices won't help with LIKE unless the column as a NOCASE collation. Use GLOB instead: ... WHERE number GLOB '1234*'; Thank you for the tip. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using LIKE to check the first digits?
Gilles <[EMAIL PROTECTED]> wrote: > At 15:40 31/01/2008 -0800, James Dennett wrote: > > > WHERE col LIKE '123%' or WHERE substr(col,1, 3) = '123' > > > >The optimizer has a decent chance of using an index for LIKE '123%' but > >I'd be surprised (and impressed) if it looks inside function calls suchas > >substr for opportunities to use indexes. > > Thanks guys. I'll remember to index the column, for higher performance. > Indices won't help with LIKE unless the column as a NOCASE collation. Use GLOB instead: ... WHERE number GLOB '1234*'; Note that "*" is the wildcard character with GLOB, not "%" as in LIKE. The above will use an index on the number column if it is available. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Using LIKE to check the first digits?
At 15:40 31/01/2008 -0800, James Dennett wrote: > WHERE col LIKE '123%' or WHERE substr(col,1, 3) = '123' The optimizer has a decent chance of using an index for LIKE '123%' but I'd be surprised (and impressed) if it looks inside function calls suchas substr for opportunities to use indexes. Thanks guys. I'll remember to index the column, for higher performance. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using LIKE to check the first digits?
Gilles wrote: Hello I'm no SQL guru, and need to look up phone numbers in a SQLite database that start with certain digits. Some customers use http://en.wikipedia.org/wiki/Direct_Inward_Dialing, which means that the first part is common to all the numbers assigned to this customer, so I don't need to enter every single one of them, and just assign the prefix, eg. "123" matches "1230001", "1230002", etc. Should I use the LIKE command for this? Does someone have an example to do this? SELECT * FROM Table WHERE Field LIKE '123%'; Use % as your wildcard, and you're good to go. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Using LIKE to check the first digits?
> -Original Message- > From: P Kishor [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 31, 2008 3:35 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Using LIKE to check the first digits? > > On 1/31/08, Gilles <[EMAIL PROTECTED]> wrote: > > Hello > > > > I'm no SQL guru, and need to look up phone numbers in a SQLite database > > that start with certain digits. Some customers use > > http://en.wikipedia.org/wiki/Direct_Inward_Dialing, which means that the > > first part is common to all the numbers assigned to this customer, so I > > don't need to enter every single one of them, and just assign the > prefix, > > eg. "123" matches "1230001", "1230002", etc. > > > > Should I use the LIKE command for this? Does someone have an example to > do > > this? > > > > WHERE col LIKE '123%' > > or WHERE substr(col,1, 3) = '123' One note: The optimizer has a decent chance of using an index for LIKE '123%' but I'd be surprised (and impressed) if it looks inside function calls such as substr for opportunities to use indexes. -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using LIKE to check the first digits?
On 1/31/08, Gilles <[EMAIL PROTECTED]> wrote: > Hello > > I'm no SQL guru, and need to look up phone numbers in a SQLite database > that start with certain digits. Some customers use > http://en.wikipedia.org/wiki/Direct_Inward_Dialing, which means that the > first part is common to all the numbers assigned to this customer, so I > don't need to enter every single one of them, and just assign the prefix, > eg. "123" matches "1230001", "1230002", etc. > > Should I use the LIKE command for this? Does someone have an example to do > this? > WHERE col LIKE '123%' or WHERE substr(col,1, 3) = '123' > Thank you. > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
