What does x'0a' mean in SQL?

user_11991071 0
Viewed 485

I'm currently working through the official Flask tutorial, and there is some SQL I have never encountered before that goes unexplained. I understand that we're inserting values into the post table, and that for body we're inserting either 'test', or x'0a', or 'body'. What does x'0a' mean? Is x some kind of format specifier? I've googled around but am not finding anything useful.

INSERT INTO post (title, body, author_id, created)
VALUES
  ('test title', 'test' || x'0a' || 'body', 1, '2018-01-01 00:00:00');
flask sqlite sql python
1 Answers

It is not in SQL, but some specific dialect of SQL. According to the code in that article it is SQLite. So from the documentation:

BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. Example: X'53514C697465'

This way it is a BLOB literal containing linefeed (LF, newline) character.

select 'q' || x'0a' || 'w' as tst
tst
q
w

db<>fiddle here

However, it's SQLite specific (and I don't clearly understand SQLite internals, but in other DBMSes will require implicit type conversion), so you may just place newline in the text which is understandable by any DBMS (and hex returns the same code point for it as shown below):

select 'q
w' as tst, hex('
') as what_is_newline
tst what_is_newline
q
w		 0A

db<>fiddle here

user_27787100
Related