Roger Binns wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 04/18/2010 11:03 PM, Dan Bishop wrote:
>
>> I've written a quoting routine too, in C++. I just truncated strings at
>> the first NUL character because I didn't think that SQLite supported them.
>>
>
> SQLite supports some weird things. In addition to supporting nulls in
> values, it also supports zero length strings as identifiers. For example:
>
> create table ""("");
>
>
>> The other special case I remember is floating-point infinity: I used
>> 9.9e999 and -9.9e999 for those.
>>
>
> Good reminder - I hadn't bothered with infinity and nan and my tests.
Don't worry about NaNs: SQLite converts them to NULL.
>>> CAST(X'43440045' AS CHAR)
>>>
>>>
>> You don't have to write it all in hex. You can just do
>>
>> 'CD' || x'00' || 'E'
>>
>
> That only works for UTF8 encoded databases. If the database is UTF16
> encoded then it completely messes up. Only a single null byte is inserted
> which causes all the remaining ones to be shifted off by one.
>
> sqlite> insert into foo values('CD' || x'00' || 'E');
> sqlite> select cast(x as blob) from foo;
> INSERT INTO "table" VALUES(X'43004400004500');
> sqlite> select typeof(x) from foo;
> text
> sqlite> select length(x) from foo;
> 4
>
> This is a pretty bad result as the text is an odd number of bytes long -
> something that is in no way valid UTF16. I'll bug report the issue.
>
>
>> My suggestion has the same issue: You have to write x'0000' for UTF-16.
>>
>
> Sadly.
>
>
>> For this case, I think the simplest approach would be to ignore BLOBs
>> altogether and register a CHR function.
>>
>
> Is that part of the SQL standard? I think I might also be able to abuse
> replace - experiments will be needed.
>
AFAIK, it's not part of the standard, but several major SQL
implementations have a CHAR or CHR function.
http://www.coderecipes.net/sql-char-chr-function.aspx
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
