sagi sagi - 3 months ago 7
SQL Question

Replace function doesn't work as expected

I'm having trouble figuring out why

doesn't work correctly.

I'm getting a string formatted as:


Up to 4000 characters .

Each spot of
represent a new string(can be up to about 15 in one string). I'm splitting it by using
- each occurence of
replace with
+ go down a line + concat the entire string again (I have another part that is splitting down the string)

I think the length of the string is some how effecting the result, though I never heard replace has some kind of limitation about the length of the string.

FROM dual)

This for some reason result in splitting the string correctly, up to
, and stops. If I remove one or more parts from the start of the string (up to the semicolumn is each part) then I'm getting it up to the next cables, according to how many I remove from the beggining.

Why is this happening ?

Thanks in advance.


If you wrap your sample input string within to_clob() in the inner query, and you wrap the resulting string within length() in the outer query, you will find that the result is 8127 characters. This answers your question, but only partially.

I am not sure why replace doesn't throw an error, or perhaps just truncate the result at 4000 characters. I got exactly the same result as you did in Oracle 11.2, with the result chopped off after 3503 characters. I just looked quickly at the Oracle documentation for replace() and it doesn't say what the behavior should be if the input is VARCHAR2 but the output is more than 4000 characters. It looks as though it performed as many substitutions as it could and then it stopped (the next substitution would have gone above 4000 characters).