@jason
I'd like to follow-up Igor's and Michael's suggestion (see their referred-to
messages below).
How about that:
a) You do a dump of your database table containing the values bob,
jean-batiste, ...
a1) select a file for your dump:
sqlite> .output FILENAME
a2) Dump the database in an SQL text format
If TABLE specified, only dump tables matching
LIKE pattern TABLE.
sqlite> .dump ?TABLE? ...
sqlite> .quit
I presume that you are working on a *NIX/Linux box.
Testwise grep the line containing 'bob' or any of the other working values in
your generated dump file
$ grep --version
GNU grep 2.6.3
$ grep -i bob mydump-file.txt
Now grep using 'jean' or any other of your values exluding the dash in
question. e.g. 'jean' only for 'jean-batiste'
$ grep -i jean mydump-file.txt
DO NOT use an editor to extract the line containing the value of e.g.
'jean-batiste' as some editors are changing values when saving files. Better
use grep instead.
If this is working then check your system for either the "hexdump" or the "od"
(for octal dump) program, e.g.
$ echo a- | hexdump
0000000 2d61 000a
0000003
$ echo a- | od -x
0000000 2d61 000a
0000003
In both cases the hex value of the letter 'a' and my dash character '-' was
shown as 2d61.
$ echo aa | od -x
0000000 6161 000a
will show you '61' as the hex value of 'a'
$ N.B.: 2 dashes used here in this example, i.e., like "echo dashdash":
echo -- | od -x
0000000 2d2d 000a
will show you '2d' as the hex value of '-'
Now look at the man page to work out the offset so that you start "dumping"
with the last letter before your dash, e.g. n-batiste ... This way you can
extract the value of your dash from your generated dump file.
If you are not working on a *NIX/Linux box then you might
a) download grep as part of the GNU text-utils
or
b) download Fedora12 Live and boot it from USB-stick
Otherwise use your generated dump file and upload it using the Online HexDump
Utility web site at
http://www.fileformat.info/tool/hexdump.htm
Finally use, e.g.
the Decimal/Hex/Binary/Octal Calculator at
http://www.indianabiomedical.com/Calculator/hex.html
or
Online Unit Converters. Common Converters. Numbers Converter
at http://www.translatorscafe.com/cafe/units-converter/numbers/c/
and take it from there.
I did something like that while parsing for German Umlaute in html with awk to
generate ASCII records for importing into SQLite - rather a RPitA (German
Umlaute, not awk or SQLite ... :-)
Cheerio
bernie
------------------------------
Message: 16
Date: Sun, 25 Apr 2010 23:28:06 -0400
From: "Igor Tandetnik" <[email protected]>
Subject: Re: [sqlite] values containing dash - not evaluated
To: [email protected]
Message-ID: <[email protected]>
Content-Type: text/plain; charset="iso-8859-1"
jason d wrote:
> 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
------------------------------
Message: 31
Date: Mon, 26 Apr 2010 06:59:24 -0500
From: "Black, Michael (IS)" <[email protected]>
Subject: Re: [sqlite] values containing dash - not evaluated
To: "General Discussion of SQLite Database" <[email protected]>
Message-ID:
<[email protected]>
Content-Type: text/plain; charset="iso-8859-1"
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
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
