CODEWITHSUNDEEP
special-charactersarabicalphanumericregexp-like
Abdul Nizar
Abdul Nizar

Reputation: 3

Identify special character in a string which has mix of Arabic and alphanumeric and special character

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@#$'

Upvotes: 0

Views: 148

Answers (1)

MatBailie
MatBailie

Reputation: 86706

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]'));

Demo; https://dbfiddle.uk/Rq1Zzopk

Upvotes: 0

Related Questions