I have a requirement where I need to identify if a string has any special/junk characters excluding Arabic and alphanumeric and space. I have tried below, but its not detecting spl character
select count(*) from table
where not regexp_like (column1,UNISTR('[\0600-\06FF\0750-\077F\0870-\089F\08A0-\08FF\FB50-\FDFF\FE70-\FEFF\0030-\0039\0041-\005A\0061-\007A]'));
column has following value 'طًيAa1@#$'
You have NOT REGEXP_LIKE(column, allowed_characters)
This means that any string with at least one allowed character will return TRUE from the regular expression, and so be excluded by the WHERE clause.
You want REGEXP_LIKE(column, disallowed_characters)
This will identify any strings that have at least one disallowed character.
You can accomplish this with ^ inside the regular expression (^ meaning 'not any of these characters')
select count(*) from table
where regexp_like (Column1, UNISTR('[^\0600-\06FF\0750-\077F\0870-\089F\08A0-\08FF\FB50-\FDFF\FE70-\FEFF\0030-\0039\0041-\005A\0061-\007A]'));