All Forums Database
nealvenna 9 posts Joined 10/12
05 Jul 2016
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)



nealvenna 9 posts Joined 10/12
06 Jul 2016

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

You must sign in to leave a comment.