How to find non English character in Oracle SQL developer-CodePudding

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

CodePudding user response：

Do you want to retrieve columns with non-English characters?

If yes then go with this link: [1]: https://community.oracle.com/tech/developers/discussion/2470289/retrieving-columns-with-non-english-characters

If it is not your question then please let us know more about this one.

CodePudding user response：

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 is 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('[\0041-\007A] ')) as non_Latin

from a

INPUT	CYRILLIC	LATIN	HIRAGANA	NON_LATIN
ぁqwかджさたなはasфやdйцhjке	джфйцке	qwasdhj	ぁかさたなはや	ぁかджさたなはфやйцке

db<>fiddle here