PL\SQL varchar2 variable with null

Question

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

"In SQL it works and eliminate the nul character" is not true, how did you get that idea? Regular expressions can't handle the nul character. On the other hand, standard string functions have no problem with it. It is not clear what you need: an update statement to replace the existing value with the one where you remove the nul character? Or just a simple function for a select query? — user5683823
– user5683823, Commented Jan 16, 2018 at 17:54
Since you seem confused about this and you are not willing to accept what I am telling you: Here is the exact text from the standard, and a link to the standard. "The interfaces specified in POSIX.1-2008 do not permit the inclusion of a NUL character in an RE or in the string to be matched. If during the operation of a standard utility a NUL is included in the text designated to be matched, that NUL may designate the end of the text string for the purposes of matching." Here: pubs.opengroup.org/onlinepubs/9699919799 — user5683823
– user5683823, Commented Jan 16, 2018 at 18:17
By there is a record in a variable, did you mean there is a column with a value? — William Robertson
– William Robertson, Commented Jan 16, 2018 at 19:05

user5683823 · Accepted Answer · 2018-01-16 18:07:15Z

2

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

answered Jan 16, 2018 at 18:07

user5683823

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

2 Comments

user1010441 Over a year ago

I got it mathguys, thank you so much for your solution.

user5683823 Over a year ago

@user1010441 - Please note that the '\0' notation will not work in Oracle standard string functions. (It does work in regular expressions, but regular expressions can't handle the nul character, so they aren't useful in your case.) To handle the nul character in standard string functions you do need to concatenate chr(0) as shown here; \0 will be interpreted verbatim, as a two-character string with no special meaning.

Collectives™ on Stack Overflow

PL\SQL varchar2 variable with null

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related