[sqlite] LIKE returns all rows
Assume a database table named Good has a column named bob. The following command will return ALL rows in the table regardless of their content: SELECT * FROM Good WHERE bob LIKE bob;
[sqlite] LIKE returns all rows
Assume a database table named Good has a column named bob. The following command will return ALL rows in the table regardless of their content: SELECT * FROM Good WHERE bob LIKE bob; Is this by design? If so, is there a workaround for this other than attempting to name all columns in a table to be so unique as to never be LIKEd? - Steve PS. Sorry about the double post, I didn't know ctrl-enter sends an email!
Re: [sqlite] LIKE returns all rows
On Saturday 23 April 2005 7:15 pm, steve wrote: Assume a database table named Good has a column named bob. The following command will return ALL rows in the table regardless of their content: SELECT * FROM Good WHERE bob LIKE bob; Is this by design? If so, is there a workaround for this other than attempting to name all columns in a table to be so unique as to never be LIKEd? Try SELECT * FROM Good WHERE bob LIKE 'bob'; Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.11.4-20a-default x86_64
Re: [sqlite] LIKE returns all rows
On Saturday 23 April 2005 7:15 pm, steve wrote: Assume a database table named Good has a column named bob. The following command will return ALL rows in the table regardless of their content: SELECT * FROM Good WHERE bob LIKE bob; Is this by design? If so, is there a workaround for this other than attempting to name all columns in a table to be so unique as to never be LIKEd? More to try SELECT * from Good WHERE bob like 'bob'; SELECT * from Good WHERE 'bob' like 'bob'; SELECT * from Good WHERE 'bob' like bob; See the pattern? Double quotes are used for column names, single quotes for values. Your SELECT * from Good WHERE bob like bob; is the same as saying SELECT * from Good WHERE 1 = 1; Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.11.4-20a-default x86_64
RE: [sqlite] LIKE returns all rows
Ah, that works just fine. Is this noted someplace in the documentation that I missed? If not: - why does it work with single quotes and not double? - Shouldn't it be added? This is all I found on the Datatypes in SQLite Version 3 page: Values specified as literals as part of SQL statements are assigned storage class TEXT if they are enclosed by single or double quotes, ... But it works, and that's what matters. Thanks! - Steve -Original Message- From: Scott Leighton [mailto:[EMAIL PROTECTED] Sent: Saturday, April 23, 2005 7:28 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] LIKE returns all rows On Saturday 23 April 2005 7:15 pm, steve wrote: Assume a database table named Good has a column named bob. The following command will return ALL rows in the table regardless of their content: SELECT * FROM Good WHERE bob LIKE bob; Is this by design? If so, is there a workaround for this other than attempting to name all columns in a table to be so unique as to never be LIKEd? Try SELECT * FROM Good WHERE bob LIKE 'bob'; Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.11.4-20a-default x86_64
Re: [sqlite] LIKE returns all rows
steve wrote: Ah, that works just fine. Is this noted someplace in the documentation that I missed? If not: - why does it work with single quotes and not double? - Shouldn't it be added? snip Double quotes and single quotes have different meanings in SQLite (as defined in ANSI SQL too). To produce string literals you MUST use single quotes. Double quotes are used to specify case sensitive field/table/etc names, or variable names with spaces in them. This is the same as Microsoft's SQLs [] characters, or MySQL backquote characters (neither are ANSI standard by the way). Therefore bob means the field name bob, and 'bob' means the literal bob. Eddy
