How to find non English character in Oracle SQL developer

user_19637567 0
Viewed 60

Query to find non English character or multibyte character for NCLOB datatype field Example : Japanese, Chinese characters.

informatica oracle sql
1 Answers

You may use regexp_replace and regex class exclusion operator (^) to extract the range you need or without exclusion to extract symbols not in the class.

Below are some examples to extract letters of specific languages as well as everything non-latin.

with a(input) as (
  select *
  from sys.odcivarchar2list(
    'ぁqwかджさたな嗢还没はas花了фやdйцhjке'
  )
)
select
  input,
  /*                                                                       */
  regexp_replace(input, unistr('[^\0400-\04FF]+')) as Cyrillic,
  regexp_replace(input, unistr('[^\0041-\007A]+')) as Latin,
  regexp_replace(input, unistr('[^\3041-\3096]+')) as Hiragana,
  regexp_replace(input, unistr('[^\4E00-\9FFF]+')) as CJK,
  regexp_replace(input, unistr('[\0041-\007A]+')) as non_Latin

from a
INPUT CYRILLIC LATIN HIRAGANA CJK NON_LATIN
ぁqwかджさたな嗢还没はas花了фやdйцhjке джфйцке qwasdhj ぁかさたなはや 嗢还没花了 ぁかджさたな嗢还没は花了фやйцке

db<>fiddle here

user_27787100
Related