regexp_similar '^.$' issues

Junior Supporter

regexp_similar '^.$' issues


For data scrubbing I have lot of hard coded values in my code. I am trying to put those values in a table. One of the condition for this scrubbing is to find length of character and code for same is character_length(name) = 1. But when I trying to emulate the same  by using '^.$', it is not catching values like ¿, ¥, Ã

please let me know if I am doing something wrong .

When I run below code  and I see this 3 values ¿, ¥, Ã

select name from email_table

where character_length(name) = 1

and name not in

(select name from email_table

where regexp_similar(translate(name USING LATIN_TO_UNICODE WITH ERROR),'^.$', 'i') = 1)


Junior Supporter

Re: regexp_similar '^.$' issues

Just one more update. We have TD14 and TD 15 on different servers and I did following query

select case when regexp_similar('¥','^.$', 'i')=1

         then 'Y'

         else 'N'

         end as output;

In case of TD 14,  I get output as 'N' and in case of TD 15 answer is 'Y'. This is more intruiging now. 

I thank Dnoeth for all his suggestions