[sqlite] How to enter Unicode character?
Hi, On Tue, Feb 9, 2016 at 3:09 AM, Dominique Devienne wrote: > On Mon, Feb 8, 2016 at 6:26 PM, Dominique Devienne > wrote: > >> OK, I retried with latest sqlite3.exe, and the results are different: >> >> C:\Users\DDevienne>sqlite3 >> SQLite version 3.10.2 2016-01-20 15:27:19 >> Enter ".help" for usage hints. >> Connected to a transient in-memory database. >> Use ".open FILENAME" to reopen on a persistent database. >> sqlite> create table t? (c?); >> sqlite> insert into t? (c?) values (char(252)); >> sqlite> insert into t? (c?) values ('?'); >> sqlite> .header on >> sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?), >> typeof(c?), quote(cast(c? as blob)) from t?; >> c?|length(c?)|length(cast(c? as >> blob))|unicode(c?)|typeof(c?)|quote(cast(c? as blob)) >> ?|1|2|252|text|X'C3BC' >> ?|1|2|129|text|X'C281' >> > > Latest unreleased 3.11 snapshot behaves the same: > > D:\my\sqlite-snapshot>shell.exe > SQLite version 3.11.0 2016-02-09 02:12:20 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> create table t (c); > sqlite> insert into t values (char(252)), ('?'); > sqlite> select c, length(c), length(cast(c as blob)), unicode(c), > typeof(c), quote(cast(c as blob)) from t; > ?|1|2|252|text|X'C3BC' > ?|1|2|129|text|X'C281' I tried it (ALT+NUMPAD keys) and it works OK. Thank you. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to enter Unicode character?
On Mon, Feb 8, 2016 at 6:26 PM, Dominique Devienne wrote: > OK, I retried with latest sqlite3.exe, and the results are different: > > C:\Users\DDevienne>sqlite3 > SQLite version 3.10.2 2016-01-20 15:27:19 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> create table t? (c?); > sqlite> insert into t? (c?) values (char(252)); > sqlite> insert into t? (c?) values ('?'); > sqlite> .header on > sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?), > typeof(c?), quote(cast(c? as blob)) from t?; > c?|length(c?)|length(cast(c? as > blob))|unicode(c?)|typeof(c?)|quote(cast(c? as blob)) > ?|1|2|252|text|X'C3BC' > ?|1|2|129|text|X'C281' > Latest unreleased 3.11 snapshot behaves the same: D:\my\sqlite-snapshot>shell.exe SQLite version 3.11.0 2016-02-09 02:12:20 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t (c); sqlite> insert into t values (char(252)), ('?'); sqlite> select c, length(c), length(cast(c as blob)), unicode(c), typeof(c), quote(cast(c as blob)) from t; ?|1|2|252|text|X'C3BC' ?|1|2|129|text|X'C281'
[sqlite] How to enter Unicode character?
On Mon, Feb 8, 2016 at 5:43 PM, Dominique Devienne wrote: > sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?) from > t?; > c?|length(c?)|length(cast(c? as blob))|unicode(c?) > ??|1|2|252 > ?|1|1|129 > > sqlite> .schema >> > CREATE TABLE t? (c?); >> > What's surprising is that the second row/value is text, and it's supposed to be UTF-8 encoded, yet as far as I know, if not value UTF-8 (0x81 is 129, which should be encoded on two bytes). sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?), typeof(c?), quote(cast(c? as blob)) from t?; c?|length(c?)|length(cast(c? as blob))|unicode(c?)|typeof(c?)|quote(cast(c? as blob)) ??|1|2|252|text|X'C3BC' ?|1|1|129|text|X'81' OK, I retried with latest sqlite3.exe, and the results are different: C:\Users\DDevienne>sqlite3 SQLite version 3.10.2 2016-01-20 15:27:19 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t? (c?); sqlite> insert into t? (c?) values (char(252)); sqlite> insert into t? (c?) values ('?'); sqlite> .header on sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?), typeof(c?), quote(cast(c? as blob)) from t?; c?|length(c?)|length(cast(c? as blob))|unicode(c?)|typeof(c?)|quote(cast(c? as blob)) ?|1|2|252|text|X'C3BC' ?|1|2|129|text|X'C281' sqlite> but still not quite the same, despite the inserted characters being logically the same (I think). x'C3BC' is the correct encoding of lower-case umlaut in UTF-8. x'C281' OTOH is the UTF-8 encoding of char(129), so the code page was not taken into account (I think), and the byte taken "as-is". No? --DD
[sqlite] How to enter Unicode character?
On Mon, Feb 8, 2016 at 5:33 PM, Dominique Devienne wrote: > On Mon, Feb 8, 2016 at 4:46 PM, Igor Korot wrote: > >> On Mon, Feb 8, 2016 at 10:36 AM, Dominique Devienne >> wrote: >> What I mean is the following: >> >> sqlite> CREATE TABLE abc(); >> >> In that line '' should be the German character which look like the >> Greek letter "beta". >> ... > > In the good old DOS days I would probably just do ALT+NUMPAD2,2,0, >> but that will most likely won't work here. >> > > It does appear to work for me. I used > https://en.wikipedia.org/wiki/Code_page_437 as a guide. > > C:\Users\DDevienne>chcp > Active code page: 437 > > ALT129 (keep pressing ALT, then press successively on keypad 1, 2, 9) > C:\Users\DDevienne>echo ? > ? > > ALT225 > C:\Users\DDevienne>echo ? > ? > > C:\Users\DDevienne>sqlite3 > SQLite version 3.8.9 2015-04-08 12:16:33 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> create table t? (c?); > sqlite> insert into t? (c?) values (char(220)); > sqlite> insert into t? (c?) values (?); > Error: no such column: ? > sqlite> insert into t? (c?) values ('?'); > sqlite> .header on > sqlite> select * from t?; > c? > ?? > ? > Sorry, slight copy/paste issue on the last message. there are two rows as expect, but only the second ALT129 entered Above I made the mistake of using char(220), which is the upper-case U umlaut. Below I use char(252) (Unicode code point), and ALT129 (Windows CP437), which are logically the same letter (lower-case u umlaut), but don't show up the same. (see https://en.wikipedia.org/wiki/%C3%9C) sqlite> create table t? (c?); sqlite> insert into t? (c?) values (char(252)); sqlite> insert into t? (c?) values ('?'); sqlite> .header on sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?) from t?; c?|length(c?)|length(cast(c? as blob))|unicode(c?) ??|1|2|252 ?|1|1|129 sqlite> .schema > CREATE TABLE t? (c?); >
[sqlite] How to enter Unicode character?
On Mon, Feb 8, 2016 at 4:46 PM, Igor Korot wrote: > On Mon, Feb 8, 2016 at 10:36 AM, Dominique Devienne > wrote: > What I mean is the following: > > sqlite> CREATE TABLE abc(); > > In that line '' should be the German character which look like the > Greek letter "beta". > ... In the good old DOS days I would probably just do ALT+NUMPAD2,2,0, > but that will most likely won't work here. > It does appear to work for me. I used https://en.wikipedia.org/wiki/Code_page_437 as a guide. C:\Users\DDevienne>chcp Active code page: 437 ALT129 (keep pressing ALT, then press successively on keypad 1, 2, 9) C:\Users\DDevienne>echo ? ? ALT225 C:\Users\DDevienne>echo ? ? C:\Users\DDevienne>sqlite3 SQLite version 3.8.9 2015-04-08 12:16:33 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t? (c?); sqlite> insert into t? (c?) values (char(220)); sqlite> insert into t? (c?) values ('?'); sqlite> .header on sqlite> select * from t?; c? ?? ?sqlite> .schema CREATE TABLE t? (c?); But you'll notice sqlite3.exe (not the latest, perhaps it matters) doesn't show the UTF-8 encoded text value correctly, but does show the table and column name correctly. But does it convert the Windows CodePage console entered text to unicode internally, to make the DB portable to another OS? My guess is that it stores the bytes as-is, which is why they show up correctly, otherwise why properly convert idents, but not text values? Pure conjecture though. --DD PS: Hopefully the above umlaut and ss above, which look correct in Chrome, will make it across correctly.
[sqlite] How to enter Unicode character?
In case it helps... In a command-prompt, ALT 156 (hold ALT while pressing 156 on the NUMERIC keypad) uses the current code-page (British pound sign - ? - for me). ?Using ALT 0163 (i.e. preceding the character-code with a zero) uses Unicode (also a ?). I don't have an sqlite3 shell to hand to test with. Graham. Sent from Samsung Mobile Original message From: Dominique Devienne Date: 08/02/2016 16:43 (GMT+00:00) To: SQLite mailing list Subject: Re: [sqlite] How to enter Unicode character? On Mon, Feb 8, 2016 at 5:33 PM, Dominique Devienne wrote: > On Mon, Feb 8, 2016 at 4:46 PM, Igor Korot wrote: > >> On Mon, Feb 8, 2016 at 10:36 AM, Dominique Devienne >> wrote: >> What I mean is the following: >> >> sqlite> CREATE TABLE abc(); >> >> In that line '' should be the German character which look like the >> Greek letter "beta". >> ... > > In the good old DOS days I would probably just do ALT+NUMPAD2,2,0, >> but that will most likely won't work here. >> > > It does appear to work for me. I used > https://en.wikipedia.org/wiki/Code_page_437 as a guide. > > C:\Users\DDevienne>chcp > Active code page: 437 > > ALT129 (keep pressing ALT, then press successively on keypad 1, 2, 9) > C:\Users\DDevienne>echo ? > ? > > ALT225 > C:\Users\DDevienne>echo ? > ? > > C:\Users\DDevienne>sqlite3 > SQLite version 3.8.9 2015-04-08 12:16:33 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> create table t? (c?); > sqlite> insert into t? (c?) values (char(220)); > sqlite> insert into t? (c?) values (?); > Error: no such column: ? > sqlite> insert into t? (c?) values ('?'); > sqlite> .header on > sqlite> select * from t?; > c? > ?? > ? > Sorry, slight copy/paste issue on the last message. there are two rows as expect, but only the second ALT129 entered Above I made the mistake of using char(220), which is the upper-case U umlaut. Below I use char(252) (Unicode code point), and ALT129 (Windows CP437), which are logically the same letter (lower-case u umlaut), but don't show up the same. (see https://en.wikipedia.org/wiki/%C3%9C) sqlite> create table t? (c?); sqlite> insert into t? (c?) values (char(252)); sqlite> insert into t? (c?) values ('?'); sqlite> .header on sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?) from t?; c?|length(c?)|length(cast(c? as blob))|unicode(c?) ??|1|2|252 ?|1|1|129 sqlite> .schema > CREATE TABLE t? (c?); > ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to enter Unicode character?
On Mon, Feb 8, 2016 at 4:28 PM, Dominique Devienne wrote: > On Mon, Feb 8, 2016 at 4:17 PM, Igor Korot wrote: > >> Now my question is: is it possible to enter a Unicode character >> > > Yes. Just use the char() built-in function. --DD > Oh, by "SQLite table contains" you meant the "table name". Sorry, I misunderstood. OT, but below shows about unicode chars (German umlaut) in column values, and not identifiers, FWIW. --DD sqlite> create table t (c); sqlite> insert into t values (char(220)); sqlite> select * from t; ?? sqlite> select length(c) from t; 1 sqlite> select length(cast (c as blob)) from t; 2 sqlite> select unicode(c) from t; 220
[sqlite] How to enter Unicode character?
On Mon, Feb 8, 2016 at 4:27 PM, Igor Korot wrote: > At the moment I'm using Win 8.1. > And the sqlite3.exe shell tool. > > So if I go with option 2, I will copy the character and the paste it > into the command. > Something like : > > sqlite3> CREATE TABLE abc(); > > right? > That's what Unix would do. So... on Windows, probably not ;). -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] How to enter Unicode character?
On Mon, Feb 8, 2016 at 4:17 PM, Igor Korot wrote: > Now my question is: is it possible to enter a Unicode character > Yes. Just use the char() built-in function. --DD
[sqlite] How to enter Unicode character?
On Mon, Feb 8, 2016 at 4:17 PM, Igor Korot wrote: > Hi, ALL, > I live in US and therefore have an English-based laptop with an > English-based keyboard. > > I am also a programmer and would like to test what happen if I have a > SQLite table > which contains a Unicode character. > It depends entirely on your environment/shell. You have multiple options: - add German a secondary keyboard layout and switch keys as needed. Doh - your keyboard is US, so you won't know where the 'sharp s' is :/. (My keyboard is physically German but it's mapped to a US layout.) - Google for "utf8 character tables" and copy/paste them. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] How to enter Unicode character?
Dominique, On Mon, Feb 8, 2016 at 11:43 AM, Dominique Devienne wrote: > On Mon, Feb 8, 2016 at 5:33 PM, Dominique Devienne > wrote: > >> On Mon, Feb 8, 2016 at 4:46 PM, Igor Korot wrote: >> >>> On Mon, Feb 8, 2016 at 10:36 AM, Dominique Devienne >>> wrote: >>> What I mean is the following: >>> >>> sqlite> CREATE TABLE abc(); >>> >>> In that line '' should be the German character which look like the >>> Greek letter "beta". >>> ... >> >> In the good old DOS days I would probably just do ALT+NUMPAD2,2,0, >>> but that will most likely won't work here. >>> >> >> It does appear to work for me. I used >> https://en.wikipedia.org/wiki/Code_page_437 as a guide. >> >> C:\Users\DDevienne>chcp >> Active code page: 437 >> >> ALT129 (keep pressing ALT, then press successively on keypad 1, 2, 9) >> C:\Users\DDevienne>echo ? >> ? >> >> ALT225 >> C:\Users\DDevienne>echo ? >> ? >> >> C:\Users\DDevienne>sqlite3 >> SQLite version 3.8.9 2015-04-08 12:16:33 >> Enter ".help" for usage hints. >> Connected to a transient in-memory database. >> Use ".open FILENAME" to reopen on a persistent database. >> sqlite> create table t? (c?); >> sqlite> insert into t? (c?) values (char(220)); >> sqlite> insert into t? (c?) values (?); >> Error: no such column: ? >> sqlite> insert into t? (c?) values ('?'); >> sqlite> .header on >> sqlite> select * from t?; >> c? >> ?? >> ? >> > > Sorry, slight copy/paste issue on the last message. > there are two rows as expect, but only the second ALT129 entered > > Above I made the mistake of using char(220), which is the upper-case U > umlaut. > Below I use char(252) (Unicode code point), and ALT129 (Windows CP437), > which are logically the same letter (lower-case u umlaut), but don't show > up the same. > (see https://en.wikipedia.org/wiki/%C3%9C) > > sqlite> create table t? (c?); > sqlite> insert into t? (c?) values (char(252)); > sqlite> insert into t? (c?) values ('?'); > sqlite> .header on > sqlite> select c?, length(c?), length(cast(c? as blob)), unicode(c?) from > t?; > c?|length(c?)|length(cast(c? as blob))|unicode(c?) > ??|1|2|252 > ?|1|1|129 > > sqlite> .schema >> > CREATE TABLE t? (c?); Thank you. I will try it tonight when I get home. >> > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to enter Unicode character?
Dominique, Stehan, On Mon, Feb 8, 2016 at 10:36 AM, Dominique Devienne wrote: > On Mon, Feb 8, 2016 at 4:28 PM, Dominique Devienne > wrote: > >> On Mon, Feb 8, 2016 at 4:17 PM, Igor Korot wrote: >> >>> Now my question is: is it possible to enter a Unicode character >>> >> >> Yes. Just use the char() built-in function. --DD >> > > Oh, by "SQLite table contains" you meant the "table name". Sorry, I > misunderstood. > OT, but below shows about unicode chars (German umlaut) in column values, > and not identifiers, FWIW. --DD > > sqlite> create table t (c); > sqlite> insert into t values (char(220)); > sqlite> select * from t; > ?? > sqlite> select length(c) from t; > 1 > sqlite> select length(cast (c as blob)) from t; > 2 > sqlite> select unicode(c) from t; > 220 What I mean is the following: sqlite> CREATE TABLE abc(); In that line '' should be the German character which look like the Greek letter "beta". Inserting the Unicode character in the table is the next step and it is easy with some SQLite function, but inserting the Unicode character into the table name or the field name, for that matter, is not. In the good old DOS days I would probably just do ALT+NUMPAD2,2,0, but that will most likely won't work here. Thank you. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to enter Unicode character?
Hi, On Mon, Feb 8, 2016 at 10:21 AM, Stephan Beal wrote: > On Mon, Feb 8, 2016 at 4:17 PM, Igor Korot wrote: > >> Hi, ALL, >> I live in US and therefore have an English-based laptop with an >> English-based keyboard. >> >> I am also a programmer and would like to test what happen if I have a >> SQLite table >> which contains a Unicode character. >> > > It depends entirely on your environment/shell. You have multiple options: > > - add German a secondary keyboard layout and switch keys as needed. Doh - > your keyboard is US, so you won't know where the 'sharp s' is :/. (My > keyboard is physically German but it's mapped to a US layout.) > > - Google for "utf8 character tables" and copy/paste them. At the moment I'm using Win 8.1. And the sqlite3.exe shell tool. So if I go with option 2, I will copy the character and the paste it into the command. Something like : sqlite3> CREATE TABLE abc(); right? Thank you. > > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ > http://gplus.to/sgbeal > "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of > those who insist on a perfect world, freedom will have to do." -- Bigby Wolf > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to enter Unicode character?
Good day, What OS are you using? Have you read up on this? http://www.fileformat.info/tip/microsoft/enter_unicode.htm Also, Notepad++ is a good text editor for displaying / saving them. (I assume that you are wanting to generate some text files as scripts) regards, Adam DeVita On Mon, Feb 8, 2016 at 10:17 AM, Igor Korot wrote: > Hi, ALL, > I live in US and therefore have an English-based laptop with an > English-based keyboard. > > I am also a programmer and would like to test what happen if I have a > SQLite table > which contains a Unicode character. > > Now my question is: is it possible to enter a Unicode character > (umlaut symbol, german 'ss' > character or maybe even something from Chinese alphabet) inside the > sqlite3.exe in > order to test my program? > > Thank you. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1
[sqlite] How to enter Unicode character?
Hi, ALL, I live in US and therefore have an English-based laptop with an English-based keyboard. I am also a programmer and would like to test what happen if I have a SQLite table which contains a Unicode character. Now my question is: is it possible to enter a Unicode character (umlaut symbol, german 'ss' character or maybe even something from Chinese alphabet) inside the sqlite3.exe in order to test my program? Thank you.