Windows 11: In Control Panel click Region and select the Administrative tab on the ensuing dialogue; for UK users, the setting Current Language for non-Unicode programs is English (United Kingdom). Click the Change System locale button to reveal that Beta: Use Unicode UTF-8 for worldwide unicode support is switched on (and so without user prompting during Windws Update).

With this setting switched on by default, Windows 11 uses code page 65001. With this code page in use, SQLite3 fails to recognise some (Unicode) characters and reports such errors as I reported here.

For convenience, my session looks as follows:

SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> .system chcp
Active code page: 65001
sqlite> create table if not exists tblCurrency as select £10.72 as price;
;
Parse error: near ";": syntax error
  create table if not exists tblCurrency as select ;
                                     error here ---^
sqlite> create table if not exists tblCurrency as select '£10.72' as price;

'  ...> ;
'  ...> ;
'  ...> '
   ...> ;
sqlite> select * from tblCurrency;
'
-

;
;
sqlite> /* when inserting '£10.72' (note - in quotes) what is inserted (after several stray continuation prompts) is unrecognisable. */

If I change the code page to 850 (used by Windows 10 by default) and re-try the same SQL expression in a new CLI session, it works BUT ...

SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> .system chcp 850
Active code page: 850
sqlite> create table if not exists tblCurrency as select '£10.72' as price;
sqlite> select * from tblCurrency;
price
------
´┐¢10.72
sqlite> /* note £ has not been written correctly */

Why is £ problematic for SQLite?

It is not. It is problematic for the program providing a "console" that sqlite3.exe and other console programs are given by the Windows operating system.

Will SQLite support code page 65001?

The sqlite3.exe CLI does not provide a replacement console for any OS.

If you create this text in an editor that can handle UTF-8: SELECT '£10.72'; , write it as a file named 'aask.sql', then run this in a sqlite3.exe session: .once aask.out .read aask.sql , then examine the file 'aask.out' in the same or similarly capable editor, you will find that sqlite3.exe handles your '£' character perfectly.

The reason you see problems when you try pasting or typing it into a console window or try getting it rendered as a glyph on a console window is that the stock Windows console and even its shiny replacement, "Windows Terminal", are stuck with a very outdated way of handling character sets.

There are a great many Windows applications which do not use the outdated Windows console API and will let you use the SQLite library to create, modify and query SQLite3 databases. These generally have no problem with character outside of the 7-bit ASCII set that Windows consoles can handle sensibly.

... you will find that sqlite3.exe handles your '£' character perfectly.

1. I tried the code snippet; the CLI executes it and the output file contains why I expected (as you suggested).

2. I tried SELECT '£10.72'; with System.Data.SQLite directly , that is, without writing the SQL statement to a file. I get the expected result.

I am not yet convinced that this is not a problem with the Windows version of SQLite3.EXE.

• The CLI is UTF-8 compliant: Therefore, I would expect

SELECT '£10.72';

to respond immediately with

'£10.72'
--------
£10.72

Version 3.41.2 does NOT respond immediately and does NOT respond with the expected result.

• If I write the SQL to a file named '£aask.sql' and then try

.read £aask.sql

I get the same behaviour as with select '£10.72';

I tried SELECT '£10.72'; with System.Data.SQLite directly ... I get the expected result.

I have no idea what this means. The System.Data.SQLite .Net library does no I/O other than its creation and access of database files. So how did you get your query with that non-7-bit code point into the library API? And how did the result get from the library API to something you could deem "expected"? (I venture that the said input and output data did not pass through the Windows Console API.)

I am not yet convinced that this is not a problem with the Windows version of SQLite3.EXE. ... The CLI is UTF-8 compliant: Therefore, I would expect ...

The CLI deals with UTF-8 on input and output. This why, when those streams come from or are directed to files, the results are unsurprising when the streams are interpreted as UTF-8 character sequences. However, such interpretation is simply not possible when the character data passes through the Windows Console API, and more particularly when it must pass through the ancient "code page" scheme which was Microsoft's early attempt to cater to non-USA customers with software that treats characters as 8-bit values.

[Yet another console interaction gone awry.]

Nothing of that nature is convincing to me. This is because disappointing character handling is completely within my expectation when characters are shoved through a channel which insists "Characters shall have no more than 8 bits."

Nothing of that nature is convincing to me.

From the command prompt, running

sqlite3 d:/sqlite32/db/chinook.db "select '£10.72';" > e:/temp/res.txt

• responds immediately

• the expected string £10.72 is written to e:/temp/res.txt and

• sqlite3.exe terminates gracefully.

Yet the same SQL fails in a CLI session.

Interesting, is it not, that as soon as the Windows Console is involved complaints start rolling in. I wonder if perhaps it is causal?

The CLI is UTF-8 compliant:

CLI is complaint, but your console isn't.

run in your console chcp and see what your console is set to.

You can chcp 65001 to display UTF-8 in cmd/powershell console, but windows's console is very limited and Microsoft advising to use "Windows Terminal" that has full support for unicode characters

run in your console chcp and see what your console is set to.

It is set to 65001; see session below.

SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> .system chcp
Active code page: 65001
sqlite> select '£10.72;';

'  ...> '
   ...> ;
'
-

When I try with a trailing £

sqlite> select '10.72£';

'  ...> '
   ...> ;
'10.72
------
10.72
sqlite>

My reading of this is that the CLI is treating £ as a continuation or terminating character.

My reading of this is that the CLI is treating £ as a continuation or terminating character.

As someone familiar with what the CLI prompts mean, I read it as "Some character after the first single-quote and before the last single-quote tripped up the usual (and correct) parsing of the input byte stream into UTF-8 code points such that the last single-quote was not interpreted as a character." When the continuation prompt starts with certain characters, including single-quote, that means that a single-quoted string literal remains open at that point in the input. The fact that you get a continuation prompt means that a terminating character was not seen. In the case you demonstrate, the semicolon was also seen as being within a string literal, and is not a terminating character in that context.

My reading of this is that the CLI is treating £ as a continuation or terminating character.

Use appropriate terminal for UTF-8: https://learn.microsoft.com/en-us/windows/terminal/

Use appropriate terminal for UTF-8:

I do NOT believe it is the console I'm using that is the problem:

Microsoft Windows [Version 10.0.22621.1413]
(c) Microsoft Corporation. All rights reserved.

C:\Users\ajaya>d:\sqlite32\sqlite3.exe ":memory:" "select '£10.72';"
£10.72

Your sqlite3.exe would be in a different location; if you have Windows 11, change as appropriate and run the same command to re-create my experience i.e.

d:\sqlite32\sqlite3.exe ":memory:" "select '£10.72';"

(see 12.3 above)

Maybe you should be looking at the byte sequences aka hexdump

Thanks for the reminder.

Alas, that fails in a similar fashion:

SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> select hex('£');
;
'  ...> '
(x1...> ;
Parse error: near ";": syntax error
  select hex('; ' ;
                  ^--- error here

However on Try it Live

select hex('£');

returns

hex('£')
C2A3

Not sure whether that is wrong , that is, I don't know if hex should be returning the unicode value, in this case U+00A3; however, C2 nstead of 00 does not look correct to me. Yet, as I point out in the Update section, Try it Live does NOT fail as the CLI on Windows 11 does.

Decimal 49827 (0xC2A3) 슣 (Korean syuh) as opposed to 163 (0x00A3) for £ might well be the issue at play here.

C2A3 is the correct UTF8 byte sequence for the UK pound symbol. You've already been told that the CLI uses UTF8. Perhaps you should ensure your Windows Terminal does too.

Having looked it up, I agree that £ has unicode point U+00A3 abd its UTF8 Hex is 2CA3.

Windows terminal handles £ correctly as I showed in Part 3 or in this reply.

To ease the reference, this is the code:

Microsoft Windows [Version 10.0.22621.1555]
(c) Microsoft Corporation. All rights reserved.

D:\>d:\sqlite32\sqlite3.exe ":memory:" "select '£10.72';"
£10.72

You've already been told that the CLI uses UTF8

At the Command Prompt, (and in several other applications, including the CLI also) I can also see £ when I hold down the ALT key and key in 0163 on the numeric keypad.

That does NOT explain to me why

• SQLite3.EXE called at the Command Prompt (as shown above) works fine
• SQLite3.EXE used as the CLI fails.

In the case that succeeds the Windows Console is not involved. In the one that fails it is. I thought we had arrived at this conclusion ages ago.

UPDATE

I can overcome the immediate problem with version 3.41.2- see my session below for details:

SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> select char(0xA3) || '10.72' as Price;
Price
------
£10.72
sqlite> create table tblCurrency as select char(0xA3) || '10.72' as Price;
sqlite> select * from tblCurrency;
Price
------
£10.72
sqlite>

Just to keep things joined up (may be).

.shell deficiency mentions this thread (I do not know C well enough to gauge the implications referred to).

A new option, possibly, for CLI: -utf8 appears to be related (I am unable to compile this version).

.shell deficiency mentions this thread (I do not know C well enough to gauge the implications referred to).

It's quite a remote tangent, being related to this thread only by mention of the CLI.

A new option, possibly, for CLI: -utf8 appears to be related (I am unable to compile this version).

It's related very closely. This thread and others like it inspired the thought, "What if we were willing to essentially take over the console and dork it however much the OS allows, without regard for what older OS versions support?"

It builds, from a cmd.exe shell whose environment supports Microsoft Visual Studio's build tools, with this invocation: nmake -f Makefile.msc , provided that a TCL interpreter has been made available. (That is needed to build the amalgamation from the sources and do some other preprocessing.)