values containing dash - not evaluated

[jason d]

Hi there everyone,
its very hard to find sqlite users so I am glad I joined this mailing list.
Recently it seems a lot of people are using sqlite which I dont get but
anyways.

I am still using sqlite2 as the libraries I use do not support sqlite3
(yet). I am using it for web development.

I ran into a strange problem recently. seems like a bug but i am no guru on
sql internals. so here goes.

Lets say I have a colum "names" and have values "bob", "jones" and
"diana-rogers"
when I use

Select * from names where "name" = "bob";

it works fine. but whenever I use "name" = "diana-rogers"
or anything with a dash in it, it wont work. I have tried with PHP and with
command line (linux)
but there is no error and i get return values as if there really does not
exist this filed.

the column name is TEXT type (if that means anything is sqlite)
I have tried with VARCHAR but I get the same result.

I have hit a brick wall with this problem and have been at it for a couple
of weeks. trying everything I know and can see on gazillionth google search.

If anyone has any info on this and can please share it will be deeply
appreciated and given virtual cookies :D

regards
Jason.

[Simon Slavin]

Post by jason d
when I use
Select * from names where "name" = "bob";
it works fine. but whenever I use "name" = "diana-rogers"

SQLite uses single quotes for strings, not double quotes. And the thing 'name' is meant to be the name of a column, not a fixed string. So try something like

SELECT * FROM names WHERE name = 'bob'

and see if that works any better.

Post by jason d
the column name is TEXT type (if that means anything is sqlite)
I have tried with VARCHAR but I get the same result.

SQLite doesn't have a VARCHAR type. It interprets it as identical to TEXT. Won't do any harm to called it VARCHAR, I'm just telling you there's no point in trying it.

Simon.

[jason d]

Hello Simon,
First , thank you for responding.

Yes maybe in the email i used double quotes, but I have actually tried every
quote/ quoteless combination.
In fact initially the SQL was in single quotes. It does not work as
expected, no results are returned an no error is thrown.


Jason

Post by Simon Slavin

Post by jason d
when I use
Select * from names where "name" = "bob";
it works fine. but whenever I use "name" = "diana-rogers"

SQLite uses single quotes for strings, not double quotes. And the thing
'name' is meant to be the name of a column, not a fixed string. So try
something like
SELECT * FROM names WHERE name = 'bob'
and see if that works any better.

Post by jason d
the column name is TEXT type (if that means anything is sqlite)
I have tried with VARCHAR but I get the same result.

SQLite doesn't have a VARCHAR type. It interprets it as identical to TEXT.
Won't do any harm to called it VARCHAR, I'm just telling you there's no
point in trying it.
Simon.
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

Post by jason d

Post by Simon Slavin
SELECT * FROM names WHERE name = 'bob'

Hello Simon,
First , thank you for responding.

You're welcome. New text below the text you're quoting, please. English is read top to bottom.

Post by jason d
Yes maybe in the email i used double quotes, but I have actually tried every
quote/ quoteless combination.
In fact initially the SQL was in single quotes.

You do not want quotes of any kind around 'name'. Try it exactly as I wrote it below and see if that works.

Post by jason d
It does not work as
expected, no results are returned an no error is thrown.

Then you have no records in your table that match your search criterion. Are you sure you really do have a record for 'bob' ? How do you prove it ?

Simon.

[jason d]

Post by Simon Slavin

Post by jason d

Post by Simon Slavin
SELECT * FROM names WHERE name = 'bob'

Hello Simon,
First , thank you for responding.

You're welcome. New text below the text you're quoting, please. English
is read top to bottom.

Post by jason d
Yes maybe in the email i used double quotes, but I have actually tried

every

Post by jason d
quote/ quoteless combination.
In fact initially the SQL was in single quotes.

You do not want quotes of any kind around 'name'. Try it exactly as I
wrote it below and see if that works.

Post by jason d
It does not work as
expected, no results are returned an no error is thrown.

Then you have no records in your table that match your search criterion.
Are you sure you really do have a record for 'bob' ? How do you prove it ?
Simon.

sorry about the top posting.

I believe you misunderstood my problem. Its not that records dont exist. and
select statement for Bob does work. a select * does display all the data.
its the names with dashes that dont shows up. and i have 40,000 records.
any with dashes do not give any result on a pure select statement. but if I
select on any other column and then work on the resultset it is ok. for
example I may choose column projectname since it does not have a dash (-) in
it. The information is clearly there, just its as if it does not equate to
anything at all.

SELECT * from Groups WHERE name = 'jean-baptiste' ; zero result.

returns zero results and yes it is in database.

sorry but i just tried this.

[Igor Tandetnik]

Post by jason d
I believe you misunderstood my problem. Its not that records dont exist. and
select statement for Bob does work. a select * does display all the data.
its the names with dashes that dont shows up. and i have 40,000 records.
any with dashes do not give any result on a pure select statement. but if I
select on any other column and then work on the resultset it is ok. for
example I may choose column projectname since it does not have a dash (-) in
it. The information is clearly there, just its as if it does not equate to
anything at all.
SELECT * from Groups WHERE name = 'jean-baptiste' ; zero result.

What does this statement return:

select name, hex(name) from Groups
where name like '%jean%';

My guess is, you either have leading and/or trailing whitespace around the value, or the dash in the middle is not U+002D (HYPHEN-MINUS) but some other Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex dump would tell.

[jason d]

Post by Igor Tandetnik

Post by jason d
I believe you misunderstood my problem. Its not that records dont exist.

and

Post by jason d
select statement for Bob does work. a select * does display all the data.
its the names with dashes that dont shows up. and i have 40,000 records.
any with dashes do not give any result on a pure select statement. but if

I

Post by jason d
select on any other column and then work on the resultset it is ok. for
example I may choose column projectname since it does not have a dash (-)

in

Post by jason d
it. The information is clearly there, just its as if it does not equate

to

Post by jason d
anything at all.
SELECT * from Groups WHERE name = 'jean-baptiste' ; zero result.

select name, hex(name) from Groups
where name like '%jean%';
My guess is, you either have leading and/or trailing whitespace around the
value, or the dash in the middle is not U+002D (HYPHEN-MINUS) but some other
Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex
dump would tell.
--
Igor Tandetnik
this is what I got. I am using SQLite version 2.8.17

SQL error: no such function: hex

[Igor Tandetnik]

Post by Igor Tandetnik

Post by Igor Tandetnik
select name, hex(name) from Groups
where name like '%jean%';
My guess is, you either have leading and/or trailing whitespace around the
value, or the dash in the middle is not U+002D (HYPHEN-MINUS) but some other
Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex
dump would tell.

this is what I got. I am using SQLite version 2.8.17
SQL error: no such function: hex

Well, I don't have the reference for SQLite2, so you'll have to do some investigative work here. Isn't there some way to inspect actual character codes comprising a string? Perhaps you can write a test application to do that.

[jason d]

Post by Igor Tandetnik

Post by Igor Tandetnik

Post by Igor Tandetnik
select name, hex(name) from Groups
where name like '%jean%';
My guess is, you either have leading and/or trailing whitespace around

the

Post by Igor Tandetnik

Post by Igor Tandetnik
value, or the dash in the middle is not U+002D (HYPHEN-MINUS) but some

other

Post by Igor Tandetnik

Post by Igor Tandetnik
Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex
dump would tell.

this is what I got. I am using SQLite version 2.8.17
SQL error: no such function: hex

Well, I don't have the reference for SQLite2, so you'll have to do some
investigative work here. Isn't there some way to inspect actual character
codes comprising a string? Perhaps you can write a test application to do
that.
Even though I could not run your test, I believe you may be on to something

here. I suspected that encoding is a problem but I cannot seem to get
anything that explains this behaviour until you mention this. Does Sqlite2
have anyway of specifying character encoding during INSERT or UPDATE? like
they do in MySQL etc etc. In which case I could test different character
encoding to see what result I am getting in my tests. I have already tried
getting output in HTML with different encodings. however it does not give
away anything visually. For example if I use PHP to generate pretty URLs
with the dashes values i get the URL correctly with the dashes in the
browser address bar. Same results in command line. The dash is always
visible. So I am theorizing here, that if I could instead insert in
different encodings and then run a test to match against the values it might
give me which encoding is correct. Thanks for helping me brain storm on this
one.

[Igor Tandetnik]

Post by jason d

Post by Igor Tandetnik
Even though I could not run your test, I believe you may be on to something

here. I suspected that encoding is a problem but I cannot seem to get
anything that explains this behaviour until you mention this. Does Sqlite2
have anyway of specifying character encoding during INSERT or UPDATE?

I don't know about SQLite2, but SQLite3 always expects strings in UTF-8 or UTF-16 (depending on what API function you use: those that want UTF-16 usually have "16" somewhere in their names). If you have a string in some other encoding, you need to convert it to UTF-{8,16} before passing it to SQLite.

However, if all your strings are pure 7-bit ASCII (and all your examples so far were such), then encoding shouldn't matter.

Post by jason d
they do in MySQL etc etc. In which case I could test different character
encoding to see what result I am getting in my tests.

Can't you just retrieve the string exactly as SQLite reports it, and dump numeric values of each individual byte (which is what built-in hex() function does in SQLite3). Post the dump here, and we'll try to figure out the encoding.

[jason d]

Post by Igor Tandetnik
I don't know about SQLite2, but SQLite3 always expects strings in UTF-8 or
UTF-16 (depending on what API function you use: those that want UTF-16
usually have "16" somewhere in their names). If you have a string in some
other encoding, you need to convert it to UTF-{8,16} before passing it to
SQLite.
However, if all your strings are pure 7-bit ASCII (and all your examples so
far were such), then encoding shouldn't matter.

I believe so but I used PHP built in functions to do INSERTs. I did specify
any encoding explicitly but it's assumed to be UTF-8 by default.

Post by Igor Tandetnik

Post by jason d
they do in MySQL etc etc. In which case I could test different character
encoding to see what result I am getting in my tests.

Can't you just retrieve the string exactly as SQLite reports it, and dump
numeric values of each individual byte (which is what built-in hex()
function does in SQLite3). Post the dump here, and we'll try to figure out
the encoding.
--

All right. I get it. I need to write something up to get the hex values of
strings in database. Get back to you soon with the dump. thanks.
Jason

[Black, Michael (IS)]

First off confirm it's not a bug with sqlite2:

sqlite> create table Groups (name varchar(10));
sqlite> insert into Groups values('bob');
sqlite> insert into Groups values('jean-baptiste');
sqlite> select * from Groups where name='jean-baptiste';
jean-baptiste

If you don't get a results this way tje sqlite2 is the problem (which I doubt).

Then do an sql .dump of your table.
sqlite> .dump Groups
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Groups (name varchar(10));
INSERT INTO "Groups" VALUES('bob');
INSERT INTO "Groups" VALUES('jean-baptiste');
COMMIT;

Then you should be able to see the SQL representation of the string and perhaps see what your problem is.

I don't know if sqlite2 has the .mode command, but if it does it's simpler yet.

sqlite> .mode insert
sqlite> select * from Groups where name like('%jean%');
INSERT INTO table VALUES('jean-baptiste');

Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems


________________________________

From: sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org on behalf of Igor Tandetnik
Sent: Sun 4/25/2010 10:28 PM
To: sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org
Subject: Re: [sqlite] values containing dash - not evaluated

Post by jason d
I believe you misunderstood my problem. Its not that records dont exist. and
select statement for Bob does work. a select * does display all the data.
its the names with dashes that dont shows up. and i have 40,000 records.
any with dashes do not give any result on a pure select statement. but if I
select on any other column and then work on the resultset it is ok. for
example I may choose column projectname since it does not have a dash (-) in
it. The information is clearly there, just its as if it does not equate to
anything at all.
SELECT * from Groups WHERE name = 'jean-baptiste' ; zero result.

What does this statement return:

select name, hex(name) from Groups
where name like '%jean%';

My guess is, you either have leading and/or trailing whitespace around the value, or the dash in the middle is not U+002D (HYPHEN-MINUS) but some other Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex dump would tell.
--
Igor Tandetnik

[jason d]

On Mon, Apr 26, 2010 at 7:59 PM, Black, Michael (IS)
Michael , thank you for this checklist.
 Here is what I have.

Post by Black, Michael (IS)
sqlite> create table Groups (name varchar(10));
sqlite> insert into Groups values('bob');
sqlite> insert into Groups values('jean-baptiste');
sqlite> select * from Groups where name='jean-baptiste';
jean-baptiste
If you don't get a results this way tje sqlite2 is the problem (which I doubt).

I get the result perfectly. So this is not sqlite2 issue. I doubt it
could be sqlite issue so I never looked at it this way. But worth a
shot.

Post by Black, Michael (IS)
Then do an sql .dump of your table.
sqlite> .dump Groups
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Groups (name varchar(10));
INSERT INTO "Groups" VALUES('bob');
INSERT INTO "Groups" VALUES('jean-baptiste');
COMMIT;
Then you should be able to see the SQL representation of the string and perhaps see what your problem is.

Ok, i tried this suggestion on both the test table we made above and
my own current DB. I see the dashes.

Post by Black, Michael (IS)
I don't know if sqlite2 has the .mode command, but if it does it's simpler yet.
sqlite> .mode insert
sqlite> select * from Groups where name like('%jean%');
INSERT INTO table VALUES('jean-baptiste');

I did not go this far. Weirdly enough all your testing showed me the
real cause. The query egenrated by PHP uses quotes around column names
for select statements. so if I use
sqlite> Select * from Groups where 'name' = 'Jean-baptiste';
// will not work
sqlite> Select * from Groups where name = 'Jean-baptiste';
// works
sqlite> Select * from "Groups" where "name" = 'jean-baptiste';
//works for double quotes around tabel and column names.

This is weird, just the day I posted all the problems I used double
quotes. I even tried with various combos. The PHP framework I have
been using always generates the last combination of quotes so it
should have worked. I don't know the reason for this happening or
something has changed since then that I cannot find. I am going to run
the the queries again to see if I get results in PHP, if not this is
not an sqlite problem anymore, mor elikely a driver or framework
issue.
Either that or I am incredibly stupid for having wasted everyone's
time. Apologies if that is the case.

jason.

[Michal Seliga]

i had similar problems and it was caused by microsoft office
it didn't used ordinary dash but some strange character with different
ascii code - so search based on it always failed
i had to convert these strange dashes to ordinary ones to make it work
try, maybe this is also your case

[Simon Slavin]

Post by Michal Seliga
i had similar problems and it was caused by microsoft office
it didn't used ordinary dash but some strange character with different
ascii code - so search based on it always failed
i had to convert these strange dashes to ordinary ones to make it work
try, maybe this is also your case

Good catch. MS Office, under some circumstances, automatically replaces the '-' (minus sign) character with a hyphen ('‐'). Technically the hyphen is the right character to use to join two words, but since it doesn't have an easy key-combination many people don't type it and like the automatic conversion that Office does. Annoyingly neither of these are actually dashes: there are n-dash ('–') and m-dash ('—') characters too. So there are four characters that all look similar but do not have the same hash value in normal text processing.

Simon.

PS: Don't get me started on figure-dashes and graphical horizontal lines. Unicode should not include graphical icons. Bah humbug.

[jason d]

Post by Simon Slavin

Post by Michal Seliga
i had similar problems and it was caused by microsoft office
it didn't used ordinary dash but some strange character with different
ascii code - so search based on it always failed
i had to convert these strange dashes to ordinary ones to make it work
try, maybe this is also your case

Good catch. MS Office, under some circumstances, automatically replaces
the '-' (minus sign) character with a hyphen ('‐'). Technically the hyphen
is the right character to use to join two words, but since it doesn't have
an easy key-combination many people don't type it and like the automatic
conversion that Office does. Annoyingly neither of these are actually
dashes: there are n-dash ('–') and m-dash ('—') characters too. So there
are four characters that all look similar but do not have the same hash
value in normal text processing.
Simon.
PS: Don't get me started on figure-dashes and graphical horizontal lines.
Unicode should not include graphical icons. Bah humbug.

Michal and Simon, Yes this is what Igor pointed out too.

Igor, I am sorry I havent had the chance to write a test to check Hex values
dump yet.
by the way I used the dash on my keyboard which is next to the number 0 if
that helps what you guys are talking about. I use eclipse or notepad++ on
windows and Nano on Linux to code as I move from place to place. all of them
show me a dash that works everywhere. in fact when I place the values
retrieved from SQlite Select * query, on the telnet request to a server I
get the reply correctly for the values containing dash. for some reason only
SQLite2 is reporting what no else can see. Let me get that Hex dump so
things will get clearer. thanks a lot