How to display blob value using x'abc' binary string literal syntax?

Question

You can easily enter a blob value using the x'abc' syntax, but is there a way to display it that way too? As shown below, selecting directly, or implicitly converting to a string using concatenation doesn't work, and shows garbage (Windows DOS prompt here).

sqlite> create table blobs (uid blob);
sqlite> insert into blobs values (x'0123456789abcdef0123456789abcdef');
sqlite> select * from blobs;
☺#Egë½═∩☺#Egë½═∩
sqlite> select ''||uid from blobs;
☺#Egë½═∩☺#Egë½═∩

I'd like the select to display:

x'0123456789abcdef0123456789abcdef'

Thanks, --DD

Mark Rushakoff · Accepted Answer · 2009-06-24 16:55:52Z

63

select quote(uid) from blobs

returns:

X'0123456789ABCDEF0123456789ABCDEF'

and if you really need it in lowercase, you can use:

select lower(quote(uid)) from blobs

edited Jun 24, 2009 at 16:55

answered Jun 24, 2009 at 16:44

Mark Rushakoff

260k47 gold badges412 silver badges401 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

simpleuser Over a year ago

sqlite3 doesn't work: select quote(uid) from test; quote(id) '☺#Egë½═∩☺#Egë½═∩'

Wolf Over a year ago

@simpleuser Using SQLite 3.45.3, I cannot reproduce the issue you describe in your comment. Is it still valid?

Collectives™ on Stack Overflow

How to display blob value using x'abc' binary string literal syntax?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related