sqlite char, ascii function

Question

Is there any work around in sqlite to cover for the lack of "char()" and "ascii()" function ?

For example:

char(97) => 'a'
ascii('a') => 97

Well, SQLite isn't really intended for performing massive amounts of processing after insertion of data, so why not do this computation in your calling code? (In C, C#, Java, C++, and many others, this is ridiculously easy). — Richard J. Ross III, Jul 22 '12 at 23:41

score 23 · Answer 1 · answered May 09 '17 at 15:22

23

I know this is too late but:

SELECT unicode('a') --ascii('a')
SELECT char(97)     --char(97)

I hope this helps :)

answered May 09 '17 at 15:22

Yashar Aliabbasi

2,663
1
23
35

2

This should be the answer. It most directly solves OP's question. And mine, too! – John Chase Jan 20 '21 at 12:29

score 3 · Answer 2 · edited Jan 18 '18 at 14:12

3

Since the time this question was written, SQLite has evidently added a CHAR() function:

SELECT CHAR(97) -- Result is 'a'

However, the closest I've gotten in the other direction is with the HEX() function:

SELECT HEX('a') -- Result is 61 (hexadecimal, is equal to 97 decimal)

Obtaining a decimal ASCII character value seems like it'd require some convoluted work...

edited Jan 18 '18 at 14:12

Ilyes

14,640
4
29
55

answered Jul 31 '15 at 18:52

Andres Cabezas Ulate

61
2

In which version of SQLite has it been added? – noraj Apr 06 '21 at 12:42

score 2 · Accepted Answer · answered Jul 23 '12 at 01:01

I suppose, if you really wanted to, you could create a "ASCII Table" Table of values with a ASCIICHAR column, and a ASCIICODE column, and populate it with the ASCII table. Then your lookups could be performed in queries / subqueries:

SELECT ASCIICHAR FROM ASCIITABLE WHERE ASCIICODE = 97;

Really though, Richard J. Ross III's comment is on the money - if you are using SQLite, you are probably accessing it through your calling code, couldn't the computation be done there?

score 0 · Answer 4 · edited May 09 '14 at 00:55

0

Although conversion is not likely, sqlite does recognize ASCII in this query:

select * from segments where substr(name, 1, 1) < 'A' or substr(name, 1, 1) > 'Z' and substr(name, 1, 1) < 'a';

Possible results: 0-9 and all ASCII < 'a'

edited May 09 '14 at 00:55

Jason Aller

3,541
28
38
38

answered May 09 '14 at 00:36

iZZoN

1
2

score 0 · Answer 5 · answered Jul 22 '14 at 18:29

It would be trivial to implement sqlite3 user functions ascii() and char() that perform the conversions in C or through another language wrapper that supports sqlite3 user functions (e.g. in python).

http://www.sqlite.org/c3ref/create_function.html
https://docs.python.org/2/library/sqlite3.html

score 0 · Answer 6 · answered Jul 21 '15 at 21:36

You could implement your own version of ascii based on the sqlite extension library that is on this site: http://sqlite.1065341.n5.nabble.com/Extension-functions-for-SQLite-in-C-for-free-td18942.html

I have modified this code to add functions, not specifically yours though. It should be pretty straightforward.

score 0 · Answer 7 · answered Jun 03 '18 at 21:29

0

For older versions use cast(X'61' as text) instead of char(97). 61 is the hexadecimal prepresentation of 97.

answered Jun 03 '18 at 21:29

Gad D Lord

6,620
12
60
106

sqlite char, ascii function

7 Answers7

Linked