oracle11g - Reverse of ascii function in Oracle

Question

When I do following query

select asciistr(first_name) from person where id = 1

the result contains '200D'.

So what is the reverse function of converting a '200D' to character, so I can find all names with that specific character.

Answer 1

You want UNISTR (and, maybe, CAST it to a VARCHAR2). If you have the table:

CREATE TABLE person ( first_name, id ) AS
SELECT 'A', 1 FROM DUAL UNION ALL
SELECT CAST( UNISTR( '200D' ) AS VARCHAR2(20) ), 1 FROM DUAL;

Then the output from your query:

select first_name,
       asciistr(first_name)
from   person
where  id = 1

Is:

FIRST_NAME | ASCIISTR(FIRST_NAME)
:--------- | :-------------------
A          | A                   
?          | 200D               

db<>fiddle here