Does the Oracle discriminant field contain Chinese

In the Oracle database how to find those fields containing Chinese data records, sometimes there is such a special need, the following collation of some of the discriminant fields contain Chinese records in several ways

1: Judging with asciistr function

Asciistr Function Description:

ASCIISTR returns the ASCII form of a character string. Non-ASCII characters are converted to \xxxx form.

The use of the Asciistr function is also based on non-ASCII characters will be converted this feature to determine the Chinese characters, as long as it contains Chinese characters, there must be \xxx such a character. and the encoding range of the Simplified Chinese characters is B0a1-f7fe. As shown in the following example

CREATE TABLE TEST

(

Name_one CHAR (24)

, Name_two VARCHAR2 (24)

, Name_thr NCHAR (24)

, Name_for NVARCHAR2 (24)

)

INSERT into TEST

SELECT ' abc10 ', ' abc20 ', ' abc30 ', ' abc40 ' from the DUAL UNION all

SELECT ' abc11 ', ' abc21 ', ' abc31 ', ' abc41 ' from the DUAL UNION all

SELECT ' abc12 ', ' abc22 ', ' abc32 ', ' abc42 ' from the DUAL UNION all

SELECT ' abc1! ', ' abc2! ', ' abc3! ', ' abc4! ' From DUAL UNION All

SELECT ' abc1# ', ' abc2# ', ' abc3# ', ' abc4# ' from the DUAL UNION all

SELECT ' abc1$ ', ' abc2$ ', ' abc3$ ', ' abc4$ ' from the DUAL UNION all

SELECT ' AB Test 1 ', ' AB Test 2 ', ' AB Test 3 ', ' AB Test 4 ' from DUAL;

Use ASCIISTR (name_one) like '%\% ' to distinguish records that have Chinese. As shown below:

SELECT Name_one from TEST WHERE asciistr (name_one) like '%\% '

But if the non-ASCII characters in the field are not just Chinese, such as Japanese, then this method is not accurate, as shown below, I insert a record containing Japanese.

INSERT into TEST

SELECT ' abこんにちは1 ', ' abこんにちは2 ', ' abこんにちは3 ', ' abこんにちは4 ' from DUAL;

COMMIT;

Sql> SELECT name_one from TEST WHERE asciistr (name_one) like '%\% ';

Name_one

------------------------

AB Test 1

Abこんにちは1

2: Use the CONVERT function to discriminate

Convert function Description:

CONVERT (Inputstring,dest_charset,source_charset)

inputstring: the string to convert

Dest_charset: Target Character Set

Source_charset: Original Character Set

Sql> SELECT * from nls_database_parameters WHERE parameter= ' Nls_characterset ';

PARAMETER VALUE

------------------------------ ----------------------------------------

Nls_characterset UTF8

Sql> SELECT Name_one, name_two from TEST WHERE name_one <> CONVERT (name_one, ' zhs16gbk ', ' UTF8 ');

3: Use function length and LENGTHB to distinguish

Use the function length and LENGTHB to distinguish, is based on the Chinese characters occupy a few bytes, and ASCII characters occupy one byte, then the comparison length and LENGTHB will be different. This will determine whether the field contains Chinese characters, but like Asciistr, if the non-ASCI character contains non-Chinese, it cannot be distinguished. There are still trickery suspects.

SELECT Name_one from TEST WHERE LENGTH (name_one)! = LENGTHB (Name_one);

About the performance of the three, basically all the same, and not that performance to a little faster. Of course, I did not have a detailed, extensive test, just a case of execution plan analysis.

Resources:

Http://www.bitscn.com/pdb/oracle/201407/240540.html

http://blog.csdn.net/yzsind/article/details/6106050

http://blog.itpub.net/9399028/viewspace-687789

Does the Oracle discriminant field contain Chinese