string literals, is there more than one escape?
(1) By Larry Brasfield (LarryBrasfield) on 2020-04-18 22:58:10 [link] [source]
I can specify a C string (aka const char) containing newlines, such as
const char weather = "It's raining\ncats and dogs.\n";
With SQL, I can create something with similar content, like this:
SELECT 'It''s raining'||char(10)||'cats and dogs.'||X'0A' as weather;
It is a little ugly, but not often necessary, so I have just accepted it. But I've always wondered, does SQLite provide a way to just specify a string literal containing control characters and such, without writing out such semi-opaque stuff? I notice that it does not become a single literal in the query plan; it becomes a set of literals given to the Concat function.
I realize that the docs do not provide for any string literal escaping beyond the SQL-standard way of getting single-quote characters into string literals. I'm hoping that either I did not read far enough or the docs are silent on a useful feature. (Yes, that's a long shot.)
I was also mildly expecting that the statement compiler would recognize the concatenation of constants as producing a single constant, to become a single String8 in the query plan. Anybody know why it does not?
(2) By jake on 2020-04-19 00:23:52 in reply to 1 [link] [source]
A string literal can contain embedded newline characters (or any UTF-8 character from what I understand):
SELECT 'It''s raining cats and dogs. ' AS weather;
An alternative might be to use the built-in
SELECT PrintF('It''s raining%scats and dogs.%s', Char(10), X'0A') AS weather;
(3) By Keith Medcalf (kmedcalf) on 2020-04-19 17:13:28 in reply to 1 [link] [source]
You are confusing something which is designed for "taking textual representations and converting them into a program" (aka, a compiler) with a data storage system.
The former (a compiler) is expected to diddle with the text that is presented to it -- that is the function of a compiler.
The latter, is expected to do what it is told and STORE and RETRIEVE data, unmolested.
They are two entirely different things.
(4.1) By Larry Brasfield (LarryBrasfield) on 2020-04-19 19:17:46 edited from 4.0 in reply to 3 [source]
Well, I don't often suffer from that confusion anymore. And I think that SQL is generally compiled before the storage engine is called upon to store anything. For example, we write literals using sequences of various characters, which only correspond to what is stored in the case of string literals that have no embedded single-quote pairs and undergo no coding change on the way through the compiler.
We like the common ways of writing literals because they are easier to write and read for most of us. In my research on string literal escaping in SQL (meaning the language accepted by ... rather than the oft-honored and seldom strictly followed standard(s)), I found a few implementations that can interpret (or "compile")
E'dogs\nand\ncats' to the compiled-as-constant-expression result of 'dogs'||char(10)||'and'||char(10)||'cats' , which maybe [a] can be obtained with the slightly more easily read 'dogs cats' .
What I really want are lone control-J separators.
[a. There is a lurking, sneaky issue with that latter literal. On a 'Nix platform, it is likely to be equivalent to the former (in eventual content.) On a Windows or Mac platform, it becomes
'dogs'||char(13)||char(10)||'and'||char(13)||char(10)||'cats' or 'dogs'||char(13)||'and'||char(13)||'cats'
respectively. And, as I mentioned, those clumsy forms can produce runtime effects that could be reliably and legibly avoided with C-style escaping. ]
As you can see, there is plenty of room for confusion on this.