{"id":36714,"date":"2024-07-27T09:58:42","date_gmt":"2024-07-26T23:58:42","guid":{"rendered":"https:\/\/database.guide\/?p=36714"},"modified":"2024-07-27T09:58:44","modified_gmt":"2024-07-26T23:58:44","slug":"understanding-postgresqls-unistr-function","status":"publish","type":"post","link":"https:\/\/database.guide\/understanding-postgresqls-unistr-function\/","title":{"rendered":"Understanding PostgreSQL’s UNISTR() Function"},"content":{"rendered":"\n

PostgreSQL has a unistr()<\/code> function that evaluates escaped Unicode characters in its argument and returns the result as text.<\/p>\n\n\n\n\n\n\n\n

Example<\/h2>\n\n\n\n

Here’s an example to demonstrate:<\/p>\n\n\n\n

SELECT unistr('\\0063\\0061\\0066\\00E9');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
caf\u00e9<\/pre>\n\n\n\n
Unicode characters can be specified as \\XXXX<\/code> (4 hexadecimal digits), \\+XXXXXX<\/code> (6 hexadecimal digits), \\uXXXX<\/code> (4 hexadecimal digits), or \\UXXXXXXXX<\/code> (8 hexadecimal digits).<\/p>\n\n\n\n
Therefore, we can do any of the following to get the same result as the above example:<\/p>\n\n\n\n
SELECT \n    unistr('\\0063\\0061\\0066\\00E9') AS \"\\XXXX\",\n    unistr('\\+000063\\+000061\\+000066\\+0000E9') AS \"+XXXXXX\",\n    unistr('\\u0063\\u0061\\u0066\\u00E9') AS \"\\uXXXX\",\n    unistr('\\U00000063\\U00000061\\U00000066\\U000000E9') AS \"\\UXXXXXXXX\";<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
 \\XXXX | +XXXXXX | \\uXXXX | \\UXXXXXXXX 
-------+---------+--------+------------
 caf\u00e9  | caf\u00e9    | caf\u00e9   | caf\u00e9<\/pre>\n\n\n\n
If we want to include a backslash, we can escape it with another backslash. In other words, to include a backslash use two backslashes.<\/p>\n\n\n\n
If the server encoding is not UTF-8, the Unicode code point identified by one of these escape sequences is converted to the actual server encoding. If it’s not possible to do that, then an error occurs.<\/p>\n\n\n\n
Null Argument<\/h2>\n\n\n\n
If the argument is null<\/code> then null<\/code> is returned:<\/p>\n\n\n\n
SELECT unistr(null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
null<\/pre>\n\n\n\n
Wrong Argument Type<\/h2>\n\n\n\n
Passing an argument of the wrong type results in an error:<\/p>\n\n\n\n
SELECT unistr(123);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR:  function unistr(integer) does not exist
LINE 1: SELECT unistr(123);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.<\/pre>\n","protected":false},"excerpt":{"rendered":"
PostgreSQL has a unistr() function that evaluates escaped Unicode characters in its argument and returns the result as text.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[93,67,20],"class_list":["post-36714","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-functions","tag-string-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36714","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=36714"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36714\/revisions"}],"predecessor-version":[{"id":36721,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36714\/revisions\/36721"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=36714"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=36714"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=36714"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}