CODEWITHSUNDEEP
postgresqlbyteasciibytea
c3win90
c3win90

Reputation: 69

Getting ascii bytes from text field in PostgreSQL

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.

Upvotes: 1

Views: 4844

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21306

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.

Upvotes: 4

Related Questions