Getting ascii bytes from text field in PostgreSQL

Question

There's a text field that we want converted to bytes using ascii in PostgreSQL.

Example:

"table" = t:116*1 (1 being first position) + a:97*2(2 being second position) + b: 99*3, etc.

Doing select ascii([text field]) only returns the ascii bytes for the first letter.

Nick Barnes · Accepted Answer · 2016-02-10 22:17:31Z

4

If you just want a bytea value corresponding to the string's byte sequence, you can use:

SELECT convert_to('åbçd€','SQL_ASCII')

If this is isn't quite what you're looking for, you can convert to a set of codepoints, and from there, you can do what you want with it:

SELECT ascii(c) FROM regexp_split_to_table('åbçd€','') s(c)

Note that these are very different in the way that they handle non-ASCII characters. Assuming a UTF8-encoded database, convert_to('å','SQL_ASCII') will give you multiple UTF8 code units, while ascii('å') returns a single Unicode codepoint.

answered Feb 10, 2016 at 22:17

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

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

2 Comments

c3win90 Over a year ago

I did select name, regexp_split_to_array(name, '')) from my_table and now it looks like this: {a,b,c,d,e}. Is it possible to split that up further so that each letter gets it's own row?

Nick Barnes Over a year ago

The query in my answer (using regexp_split_to_table()) will do that

Collectives™ on Stack Overflow

Getting ascii bytes from text field in PostgreSQL

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related