[sqlite] WHERE = does not work
I have an sqlite3 database which I can't query with WHERE =. I have to use WHERE like. Any ideas why this is? For example I have a resourceType column that has as some of its entries (over 50) 'PSM'. SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing. SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM entries. What's the diff? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
With the like clause you have to use the % sign as a wildcard. So resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite website has excellent docs on standard SQL. -Original Message- From: ecforu ecforus...@gmail.com Sent: Friday, April 30, 2010 09:22 To: sqlite-users@sqlite.org Subject: [sqlite] WHERE = does not work I have an sqlite3 database which I can't query with WHERE =. I have to use WHERE like. Any ideas why this is? For example I have a resourceType column that has as some of its entries (over 50) 'PSM'. SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing. SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM entries. What's the diff? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
Ecforu, Re: What's the diff? In sqlite, LIKE without a % (percent-sign ) would be a case-insensitive search, whereas == would be case-sensitive. sqlite select 'cat' like 'CAT'; 1 sqlite select 'cat' == 'CAT'; 0 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
Apparently you must by typeing something wrong.
This works for me:
create table t(resourceType varchar);
insert into t values('PSM');
select * from t where resourceType = 'PSM';
PSM
select * from t where resourceType like 'PSM';
PSM
Does this work for you?
I'm using 3.6.23.1
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:22 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] WHERE = does not work
I have an sqlite3 database which I can't query with WHERE =. I have to use
WHERE like.
Any ideas why this is?
For example I have a resourceType column that has as some of its entries
(over 50) 'PSM'.
SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing.
SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM
entries.
What's the diff?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
But the like WHERE clause works the way it is. Its the = that isn't working. I would rather use = than like. I'm just using like for now because it works. Thanks On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer tsaw...@mybowlingdiary.com wrote: With the like clause you have to use the % sign as a wildcard. So resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite website has excellent docs on standard SQL. -Original Message- From: ecforu ecforus...@gmail.com Sent: Friday, April 30, 2010 09:22 To: sqlite-users@sqlite.org Subject: [sqlite] WHERE = does not work I have an sqlite3 database which I can't query with WHERE =. I have to use WHERE like. Any ideas why this is? For example I have a resourceType column that has as some of its entries (over 50) 'PSM'. SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing. SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM entries. What's the diff? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
You are likely getting the case insensitive result with like.
sqlite create table t(resourceType varchar);
sqlite insert into t values('PSM');
sqlite insert into t values('psm');
sqlite select * from t where resourceType = 'PSM';
PSM
sqlite select * from t where resourceType like 'PSM';
PSM
psm
sqlite select * from t where upper(resourceType) = 'PSM';
PSM
psm
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
But the like WHERE clause works the way it is. Its the = that isn't
working. I would rather use = than like. I'm just using like for now
because it works.
Thanks
On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer
tsaw...@mybowlingdiary.com wrote:
With the like clause you have to use the % sign as a wildcard. So
resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite website
has excellent docs on standard SQL.
-Original Message-
From: ecforu ecforus...@gmail.com
Sent: Friday, April 30, 2010 09:22
To: sqlite-users@sqlite.org
Subject: [sqlite] WHERE = does not work
I have an sqlite3 database which I can't query with WHERE =. I have to use
WHERE like.
Any ideas why this is?
For example I have a resourceType column that has as some of its entries
(over 50) 'PSM'.
SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing.
SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM
entries.
What's the diff?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
On Fri, Apr 30, 2010 at 9:22 AM, ecforu ecforus...@gmail.com wrote: I have an sqlite3 database which I can't query with WHERE =. I have to use WHERE like. Any ideas why this is? For example I have a resourceType column that has as some of its entries (over 50) 'PSM'. SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing. SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM entries. What does this give you? SELECT * FROM MyTable WHERE resourceType = 'PSM' COLLATE nocase; What's the diff? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- - D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
I don't think it is a case issue. See below from sqlite3 command line.
Also one thing to note - I build the database from c API. I don't know if
that makes a difference.
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType like
'PSM' LIMIT 10;
timeStamp|resourceType
2010-04-28 17:46:45.316|PSM
2010-04-28 17:46:49.854|PSM
2010-04-28 17:46:52.830|PSM
2010-04-28 17:47:04.939|PSM
2010-04-28 17:47:06.776|PSM
2010-04-28 17:47:08.846|PSM
2010-04-28 17:47:12.001|PSM
2010-04-28 17:47:13.845|PSM
2010-04-28 17:47:16.837|PSM
2010-04-28 17:47:18.846|PSM
sqlite
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType =
'PSM' LIMIT 10;
sqlite
sqlite
sqlite
THANKS
On Fri, Apr 30, 2010 at 9:35 AM, Black, Michael (IS) michael.bla...@ngc.com
wrote:
You are likely getting the case insensitive result with like.
sqlite create table t(resourceType varchar);
sqlite insert into t values('PSM');
sqlite insert into t values('psm');
sqlite select * from t where resourceType = 'PSM';
PSM
sqlite select * from t where resourceType like 'PSM';
PSM
psm
sqlite select * from t where upper(resourceType) = 'PSM';
PSM
psm
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
But the like WHERE clause works the way it is. Its the = that isn't
working. I would rather use = than like. I'm just using like for now
because it works.
Thanks
On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer
tsaw...@mybowlingdiary.com wrote:
With the like clause you have to use the % sign as a wildcard. So
resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite
website
has excellent docs on standard SQL.
-Original Message-
From: ecforu ecforus...@gmail.com
Sent: Friday, April 30, 2010 09:22
To: sqlite-users@sqlite.org
Subject: [sqlite] WHERE = does not work
I have an sqlite3 database which I can't query with WHERE =. I have to
use
WHERE like.
Any ideas why this is?
For example I have a resourceType column that has as some of its entries
(over 50) 'PSM'.
SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing.
SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM
entries.
What's the diff?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
Is it possible there is a null, tab, newline or other invisible character?
Try
select timeStamp, '' || resourceType || 'xx' From MyTable where
resourceType like 'PSM' LIMIT 10;
On Fri, Apr 30, 2010 at 9:53 AM, ecforu ecforus...@gmail.com wrote:
I don't think it is a case issue. See below from sqlite3 command line.
Also one thing to note - I build the database from c API. I don't know if
that makes a difference.
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType like
'PSM' LIMIT 10;
timeStamp|resourceType
2010-04-28 17:46:45.316|PSM
2010-04-28 17:46:49.854|PSM
2010-04-28 17:46:52.830|PSM
2010-04-28 17:47:04.939|PSM
2010-04-28 17:47:06.776|PSM
2010-04-28 17:47:08.846|PSM
2010-04-28 17:47:12.001|PSM
2010-04-28 17:47:13.845|PSM
2010-04-28 17:47:16.837|PSM
2010-04-28 17:47:18.846|PSM
sqlite
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType =
'PSM' LIMIT 10;
sqlite
sqlite
sqlite
THANKS
On Fri, Apr 30, 2010 at 9:35 AM, Black, Michael (IS)
michael.bla...@ngc.com
wrote:
You are likely getting the case insensitive result with like.
sqlite create table t(resourceType varchar);
sqlite insert into t values('PSM');
sqlite insert into t values('psm');
sqlite select * from t where resourceType = 'PSM';
PSM
sqlite select * from t where resourceType like 'PSM';
PSM
psm
sqlite select * from t where upper(resourceType) = 'PSM';
PSM
psm
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
But the like WHERE clause works the way it is. Its the = that isn't
working. I would rather use = than like. I'm just using like for now
because it works.
Thanks
On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer
tsaw...@mybowlingdiary.com wrote:
With the like clause you have to use the % sign as a wildcard. So
resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite
website
has excellent docs on standard SQL.
-Original Message-
From: ecforu ecforus...@gmail.com
Sent: Friday, April 30, 2010 09:22
To: sqlite-users@sqlite.org
Subject: [sqlite] WHERE = does not work
I have an sqlite3 database which I can't query with WHERE =. I have to
use
WHERE like.
Any ideas why this is?
For example I have a resourceType column that has as some of its
entries
(over 50) 'PSM'.
SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing.
SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM
entries.
What's the diff?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
Care to show us the function in C where you're doing the insert?
At least the code where you prepare the string and insert it.
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
I don't think it is a case issue. See below from sqlite3 command line.
Also one thing to note - I build the database from c API. I don't know if
that makes a difference.
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType like
'PSM' LIMIT 10;
timeStamp|resourceType
2010-04-28 17:46:45.316|PSM
2010-04-28 17:46:49.854|PSM
2010-04-28 17:46:52.830|PSM
2010-04-28 17:47:04.939|PSM
2010-04-28 17:47:06.776|PSM
2010-04-28 17:47:08.846|PSM
2010-04-28 17:47:12.001|PSM
2010-04-28 17:47:13.845|PSM
2010-04-28 17:47:16.837|PSM
2010-04-28 17:47:18.846|PSM
sqlite
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType =
'PSM' LIMIT 10;
sqlite
sqlite
sqlite
THANKS
On Fri, Apr 30, 2010 at 9:35 AM, Black, Michael (IS) michael.bla...@ngc.com
wrote:
You are likely getting the case insensitive result with like.
sqlite create table t(resourceType varchar);
sqlite insert into t values('PSM');
sqlite insert into t values('psm');
sqlite select * from t where resourceType = 'PSM';
PSM
sqlite select * from t where resourceType like 'PSM';
PSM
psm
sqlite select * from t where upper(resourceType) = 'PSM';
PSM
psm
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
But the like WHERE clause works the way it is. Its the = that isn't
working. I would rather use = than like. I'm just using like for now
because it works.
Thanks
On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer
tsaw...@mybowlingdiary.com wrote:
With the like clause you have to use the % sign as a wildcard. So
resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite
website
has excellent docs on standard SQL.
-Original Message-
From: ecforu ecforus...@gmail.com
Sent: Friday, April 30, 2010 09:22
To: sqlite-users@sqlite.org
Subject: [sqlite] WHERE = does not work
I have an sqlite3 database which I can't query with WHERE =. I have to
use
WHERE like.
Any ideas why this is?
For example I have a resourceType column that has as some of its entries
(over 50) 'PSM'.
SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing.
SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM
entries.
What's the diff?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
Also...what do you get from a .dump ?? Any extra chars should show up in the
SQL statements.
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
I don't think it is a case issue. See below from sqlite3 command line.
Also one thing to note - I build the database from c API. I don't know if
that makes a difference.
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType like
'PSM' LIMIT 10;
timeStamp|resourceType
2010-04-28 17:46:45.316|PSM
2010-04-28 17:46:49.854|PSM
2010-04-28 17:46:52.830|PSM
2010-04-28 17:47:04.939|PSM
2010-04-28 17:47:06.776|PSM
2010-04-28 17:47:08.846|PSM
2010-04-28 17:47:12.001|PSM
2010-04-28 17:47:13.845|PSM
2010-04-28 17:47:16.837|PSM
2010-04-28 17:47:18.846|PSM
sqlite
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType =
'PSM' LIMIT 10;
sqlite
sqlite
sqlite
THANKS
On Fri, Apr 30, 2010 at 9:35 AM, Black, Michael (IS) michael.bla...@ngc.com
wrote:
You are likely getting the case insensitive result with like.
sqlite create table t(resourceType varchar);
sqlite insert into t values('PSM');
sqlite insert into t values('psm');
sqlite select * from t where resourceType = 'PSM';
PSM
sqlite select * from t where resourceType like 'PSM';
PSM
psm
sqlite select * from t where upper(resourceType) = 'PSM';
PSM
psm
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
But the like WHERE clause works the way it is. Its the = that isn't
working. I would rather use = than like. I'm just using like for now
because it works.
Thanks
On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer
tsaw...@mybowlingdiary.com wrote:
With the like clause you have to use the % sign as a wildcard. So
resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite
website
has excellent docs on standard SQL.
-Original Message-
From: ecforu ecforus...@gmail.com
Sent: Friday, April 30, 2010 09:22
To: sqlite-users@sqlite.org
Subject: [sqlite] WHERE = does not work
I have an sqlite3 database which I can't query with WHERE =. I have to
use
WHERE like.
Any ideas why this is?
For example I have a resourceType column that has as some of its entries
(over 50) 'PSM'.
SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing.
SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM
entries.
What's the diff?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
On Fri, Apr 30, 2010 at 9:53 AM, ecforu ecforus...@gmail.com wrote:
I don't think it is a case issue. See below from sqlite3 command line.
Also one thing to note - I build the database from c API. I don't know if
that makes a difference.
Please email me your database file by private email and I will have a look.
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType like
'PSM' LIMIT 10;
timeStamp|resourceType
2010-04-28 17:46:45.316|PSM
2010-04-28 17:46:49.854|PSM
2010-04-28 17:46:52.830|PSM
2010-04-28 17:47:04.939|PSM
2010-04-28 17:47:06.776|PSM
2010-04-28 17:47:08.846|PSM
2010-04-28 17:47:12.001|PSM
2010-04-28 17:47:13.845|PSM
2010-04-28 17:47:16.837|PSM
2010-04-28 17:47:18.846|PSM
sqlite
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType =
'PSM' LIMIT 10;
sqlite
sqlite
sqlite
THANKS
On Fri, Apr 30, 2010 at 9:35 AM, Black, Michael (IS)
michael.bla...@ngc.com
wrote:
You are likely getting the case insensitive result with like.
sqlite create table t(resourceType varchar);
sqlite insert into t values('PSM');
sqlite insert into t values('psm');
sqlite select * from t where resourceType = 'PSM';
PSM
sqlite select * from t where resourceType like 'PSM';
PSM
psm
sqlite select * from t where upper(resourceType) = 'PSM';
PSM
psm
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
But the like WHERE clause works the way it is. Its the = that isn't
working. I would rather use = than like. I'm just using like for now
because it works.
Thanks
On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer
tsaw...@mybowlingdiary.com wrote:
With the like clause you have to use the % sign as a wildcard. So
resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite
website
has excellent docs on standard SQL.
-Original Message-
From: ecforu ecforus...@gmail.com
Sent: Friday, April 30, 2010 09:22
To: sqlite-users@sqlite.org
Subject: [sqlite] WHERE = does not work
I have an sqlite3 database which I can't query with WHERE =. I have to
use
WHERE like.
Any ideas why this is?
For example I have a resourceType column that has as some of its
entries
(over 50) 'PSM'.
SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing.
SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM
entries.
What's the diff?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
-
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
On 30 April 2010 14:59, Adam DeVita adev...@verifeye.com wrote: Is it possible there is a null, tab, newline or other invisible character? Try select timeStamp, '' || resourceType || 'xx' From MyTable where resourceType like 'PSM' LIMIT 10; Following from Adam's suggestion: null chars in your data (the terminators of your C strings perhaps?) reproduce the effect you report: SQLite version 3.6.11 Enter .help for instructions sqlite sqlite create table tst( t timestamp, resType text ); sqlite sqlite insert into tst values( datetime( 'now' ), 'PSM' ); sqlite sqlite insert into tst values( datetime( 'now' ), 'PSM'||X'00' ); sqlite sqlite select * from tst; 2010-04-30 14:11:07|PSM 2010-04-30 14:11:17|PSM sqlite sqlite select * from tst where resType='PSM'; 2010-04-30 14:11:07|PSM sqlite select * from tst where resType like 'PSM'; 2010-04-30 14:11:07|PSM 2010-04-30 14:11:17|PSM Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
this was my first thought so I did a dump to a file and looked at in hex -
there were no extra characters. I even tried looking at the db file with a
hex editor and I could see the PSM text and no extra characters around it
(except the NULLs on either side which I assume separates the columns).
On Fri, Apr 30, 2010 at 10:02 AM, Black, Michael (IS)
michael.bla...@ngc.com wrote:
Also...what do you get from a .dump ?? Any extra chars should show up in
the SQL statements.
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
I don't think it is a case issue. See below from sqlite3 command line.
Also one thing to note - I build the database from c API. I don't know if
that makes a difference.
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType like
'PSM' LIMIT 10;
timeStamp|resourceType
2010-04-28 17:46:45.316|PSM
2010-04-28 17:46:49.854|PSM
2010-04-28 17:46:52.830|PSM
2010-04-28 17:47:04.939|PSM
2010-04-28 17:47:06.776|PSM
2010-04-28 17:47:08.846|PSM
2010-04-28 17:47:12.001|PSM
2010-04-28 17:47:13.845|PSM
2010-04-28 17:47:16.837|PSM
2010-04-28 17:47:18.846|PSM
sqlite
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType =
'PSM' LIMIT 10;
sqlite
sqlite
sqlite
THANKS
On Fri, Apr 30, 2010 at 9:35 AM, Black, Michael (IS)
michael.bla...@ngc.com
wrote:
You are likely getting the case insensitive result with like.
sqlite create table t(resourceType varchar);
sqlite insert into t values('PSM');
sqlite insert into t values('psm');
sqlite select * from t where resourceType = 'PSM';
PSM
sqlite select * from t where resourceType like 'PSM';
PSM
psm
sqlite select * from t where upper(resourceType) = 'PSM';
PSM
psm
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
But the like WHERE clause works the way it is. Its the = that isn't
working. I would rather use = than like. I'm just using like for now
because it works.
Thanks
On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer
tsaw...@mybowlingdiary.com wrote:
With the like clause you have to use the % sign as a wildcard. So
resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite
website
has excellent docs on standard SQL.
-Original Message-
From: ecforu ecforus...@gmail.com
Sent: Friday, April 30, 2010 09:22
To: sqlite-users@sqlite.org
Subject: [sqlite] WHERE = does not work
I have an sqlite3 database which I can't query with WHERE =. I have to
use
WHERE like.
Any ideas why this is?
For example I have a resourceType column that has as some of its
entries
(over 50) 'PSM'.
SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing.
SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM
entries.
What's the diff?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
Show us your dump output -- there should be no nulls.
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 10:56 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
this was my first thought so I did a dump to a file and looked at in hex -
there were no extra characters. I even tried looking at the db file with a
hex editor and I could see the PSM text and no extra characters around it
(except the NULLs on either side which I assume separates the columns).
On Fri, Apr 30, 2010 at 10:02 AM, Black, Michael (IS)
michael.bla...@ngc.com wrote:
Also...what do you get from a .dump ?? Any extra chars should show up in
the SQL statements.
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
I don't think it is a case issue. See below from sqlite3 command line.
Also one thing to note - I build the database from c API. I don't know if
that makes a difference.
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType like
'PSM' LIMIT 10;
timeStamp|resourceType
2010-04-28 17:46:45.316|PSM
2010-04-28 17:46:49.854|PSM
2010-04-28 17:46:52.830|PSM
2010-04-28 17:47:04.939|PSM
2010-04-28 17:47:06.776|PSM
2010-04-28 17:47:08.846|PSM
2010-04-28 17:47:12.001|PSM
2010-04-28 17:47:13.845|PSM
2010-04-28 17:47:16.837|PSM
2010-04-28 17:47:18.846|PSM
sqlite
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType =
'PSM' LIMIT 10;
sqlite
sqlite
sqlite
THANKS
On Fri, Apr 30, 2010 at 9:35 AM, Black, Michael (IS)
michael.bla...@ngc.com
wrote:
You are likely getting the case insensitive result with like.
sqlite create table t(resourceType varchar);
sqlite insert into t values('PSM');
sqlite insert into t values('psm');
sqlite select * from t where resourceType = 'PSM';
PSM
sqlite select * from t where resourceType like 'PSM';
PSM
psm
sqlite select * from t where upper(resourceType) = 'PSM';
PSM
psm
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
But the like WHERE clause works the way it is. Its the = that isn't
working. I would rather use = than like. I'm just using like for now
because it works.
Thanks
On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer
tsaw...@mybowlingdiary.com wrote:
With the like clause you have to use the % sign as a wildcard. So
resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite
website
has excellent docs on standard SQL.
-Original Message-
From: ecforu ecforus...@gmail.com
Sent: Friday, April 30, 2010 09:22
To: sqlite-users@sqlite.org
Subject: [sqlite] WHERE = does not work
I have an sqlite3 database which I can't query with WHERE =. I have to
use
WHERE like.
Any ideas why this is?
For example I have a resourceType column that has as some of its
entries
(over 50) 'PSM'.
SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing.
SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM
entries.
What's the diff?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin
Re: [sqlite] WHERE = does not work
On 30 April 2010 16:56, ecforu ecforus...@gmail.com wrote:
this was my first thought so I did a dump to a file and looked at in hex -
there were no extra characters. I even tried looking at the db file with a
hex editor and I could see the PSM text and no extra characters around it
(except the NULLs on either side which I assume separates the columns).
.dump does not preserve nulls in text fields:
SQLite version 3.6.11
Enter .help for instructions
sqlite
sqlite create table tst( t timestamp, resType text );
sqlite insert into tst values( datetime( 'now' ), 'PSM' );
sqlite insert into tst values( datetime( 'now' ), 'PSM'||X'00' );
sqlite
sqlite insert into tst values( datetime( 'now' ), 'PSM'||X'00'||'more text' );
sqlite
sqlite .dump
BEGIN TRANSACTION;
CREATE TABLE tst( t timestamp, resType text );
INSERT INTO tst VALUES('2010-04-30 16:01:07','PSM');
INSERT INTO tst VALUES('2010-04-30 16:01:07','PSM');
INSERT INTO tst VALUES('2010-04-30 16:03:40','PSM');
COMMIT;
sqlite
sqlite select hex( resType ) from tst;
50534D
50534D00
50534D006D6F72652074657874
sqlite
try
SELECT hex( resType ) FROM MyTable;
Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
Would this query help determine if any extraneous characters present? SELECT * FROM MyTable WHERE LENGTH(resourceType) 3 AND resourceType LIKE 'PSM' ; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
I am not sure if this is your issue exactly but I had similar problems when I
started on SQLite a while ago. It turned out that my table definition was
case-sensitive and therefore = was not working for me and I had to use LIKE. I
changed my table definition with COLLATE NO CASE and the problem was solved,
that is, = worked. Another problem in using LIKE was very slow search (as
indices do not work with LIKE).
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Simon Davies
Sent: Friday, April 30, 2010 9:06 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
On 30 April 2010 16:56, ecforu ecforus...@gmail.com wrote:
this was my first thought so I did a dump to a file and looked at in hex -
there were no extra characters. I even tried looking at the db file with a
hex editor and I could see the PSM text and no extra characters around it
(except the NULLs on either side which I assume separates the columns).
.dump does not preserve nulls in text fields:
SQLite version 3.6.11
Enter .help for instructions
sqlite
sqlite create table tst( t timestamp, resType text );
sqlite insert into tst values( datetime( 'now' ), 'PSM' );
sqlite insert into tst values( datetime( 'now' ), 'PSM'||X'00' );
sqlite
sqlite insert into tst values( datetime( 'now' ), 'PSM'||X'00'||'more text' );
sqlite
sqlite .dump
BEGIN TRANSACTION;
CREATE TABLE tst( t timestamp, resType text );
INSERT INTO tst VALUES('2010-04-30 16:01:07','PSM');
INSERT INTO tst VALUES('2010-04-30 16:01:07','PSM');
INSERT INTO tst VALUES('2010-04-30 16:03:40','PSM');
COMMIT;
sqlite
sqlite select hex( resType ) from tst;
50534D
50534D00
50534D006D6F72652074657874
sqlite
try
SELECT hex( resType ) FROM MyTable;
Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
Is is possible the character encoding is different?
On 4/30/2010 6:59 AM, Adam DeVita wrote:
Is it possible there is a null, tab, newline or other invisible character?
Try
select timeStamp, '' || resourceType || 'xx' From MyTable where
resourceType like 'PSM' LIMIT 10;
On Fri, Apr 30, 2010 at 9:53 AM, ecforuecforus...@gmail.com wrote:
I don't think it is a case issue. See below from sqlite3 command line.
Also one thing to note - I build the database from c API. I don't know if
that makes a difference.
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType like
'PSM' LIMIT 10;
timeStamp|resourceType
2010-04-28 17:46:45.316|PSM
2010-04-28 17:46:49.854|PSM
2010-04-28 17:46:52.830|PSM
2010-04-28 17:47:04.939|PSM
2010-04-28 17:47:06.776|PSM
2010-04-28 17:47:08.846|PSM
2010-04-28 17:47:12.001|PSM
2010-04-28 17:47:13.845|PSM
2010-04-28 17:47:16.837|PSM
2010-04-28 17:47:18.846|PSM
sqlite
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType =
'PSM' LIMIT 10;
sqlite
sqlite
sqlite
THANKS
On Fri, Apr 30, 2010 at 9:35 AM, Black, Michael (IS)
michael.bla...@ngc.com
wrote:
You are likely getting the case insensitive result with like.
sqlite create table t(resourceType varchar);
sqlite insert into t values('PSM');
sqlite insert into t values('psm');
sqlite select * from t where resourceType = 'PSM';
PSM
sqlite select * from t where resourceType like 'PSM';
PSM
psm
sqlite select * from t where upper(resourceType) = 'PSM';
PSM
psm
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
But the like WHERE clause works the way it is. Its the = that isn't
working. I would rather use = than like. I'm just using like for now
because it works.
Thanks
On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer
tsaw...@mybowlingdiary.com wrote:
With the like clause you have to use the % sign as a wildcard. So
resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite
website
has excellent docs on standard SQL.
-Original Message-
From: ecforuecforus...@gmail.com
Sent: Friday, April 30, 2010 09:22
To: sqlite-users@sqlite.org
Subject: [sqlite] WHERE = does not work
I have an sqlite3 database which I can't query with WHERE =. I have to
use
WHERE like.
Any ideas why this is?
For example I have a resourceType column that has as some of its
entries
(over 50) 'PSM'.
SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing.
SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM
entries.
What's the diff?
Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
