I have a query in SQL Developer and there is a record in a variable (varchar2) that is JOYABCD. Looking into it usng notepad++ is written like: JOYnulABCD. So in reality there in a nul character between JOY and ABCD.
Is there a way in PL\SQL to get-rid of this nul? I tryed with the following: regexp_replace(name_variable, '\0', NULL)
but R says: nul character not allowed
Why, R say that? In slq it works and eliminate the nul character..
Thank you, Andrea
If the string str contains chr(0) (the nul character), you can remove it from the string like so:
replace(str, chr(0))
This will be the same string, with every occurrence of chr(0) removed from it.
Demo:
Here I create a string str in a with clause, but in real life it should be your variable or column name. Then, I "replace" chr(0) with regular expression (which as you shall see does NOTHING), and with standard REPLACE. I use the DUMP function to show the actual characters in the strings. As you shall see, the fourth character is nul (value 0) for the original string and the regexp result, but the 0 is deleted when I use the standard Oracle function REPLACE (not based on regular expressions).
with inputs as ( select 'JOY' || chr(0) || 'ABC' as str from dual )
select dump(str) as dump_str,
dump(regexp_replace(str, chr(0))) as dump_after_regexp_replace,
dump(replace(str, chr(0))) as dump_after_replace
from inputs
;
DUMP_STR DUMP_AFTER_REGEXP_REPLACE DUMP_AFTER_REPLACE
-------------------------------- -------------------------------- ------------------------------
Typ=1 Len=7: 74,79,89,0,65,66,67 Typ=1 Len=7: 74,79,89,0,65,66,67 Typ=1 Len=6: 74,79,89,65,66,67