How to determine whether a string contains Chinese characters in Oracle

How to determine whether a string contains Chinese characters in Oracle

I. 1 blog document structure diagram

650) this.width=650; "title=" wps3d5d.tmp "border=" 0 "alt=" wps3d5d.tmp "src=" http://s3.51cto.com/wyfs02/M02/6E/06/ Wkiom1vxp23zkq1caad_dmbe8j4132.jpg "" 390 "height=" 287 "/>

I. 2 Foreword part I. 2.1 Guide

Technical enthusiasts, after reading this article, you can master the following skills, you can also learn some other knowledge you do not know, ~o (∩_∩) o~:

① the judgment of full-width characters, or a string containing Chinese characters

If there are errors or imperfections in this article please correct me, itpub message or QQ can be, your criticism refers to my writing is the greatest motivation.

I. 2.2 Introduction to the experimental environment

11.2.0.3 RHEL6.5

I. 2.3 Introduction to this article

See the Netizen asked, how to query the table of a field data is not included in the full-width character ah? This question involves several functions: to_single_byte, Length, and LENGTHB, which I studied before when I was developing how to tell if a string contains Chinese, which is actually the same as this, and see the experimental part.

650) this.width=650; "title=" wps3d5e.tmp "border=" 0 "alt=" wps3d5e.tmp "src=" http://s3.51cto.com/wyfs02/M00/6E/06/ Wkiom1vxp26sd3nxaab4lz_bwuu051.jpg "" 419 "height=" 133 "/>

I. 3 experimental PART I. 3.1 LENGTHB and length functions combined with to_single_byte function

---contain Chinese characters, strictly speaking, contain full-width characters.

SELECT l.name,

Length(l.name),

LENGTHB(l.name)

From Xb_link l

WHERE length(l.name) ! = LENGTHB(l.name)

and length(l.name) < ;

650) this.width=650; "title=" wps3d6e.tmp "border=" 0 "alt=" wps3d6e.tmp "src=" http://s3.51cto.com/wyfs02/M01/6E/06/ Wkiom1vxp26gr9_kaaeb6nxnppk658.jpg "" 503 "height="/>

The following data also satisfies the criteria:

SELECT l.id,

L.name

From Xb_link l

WHERE length(l.name) ! = LENGTHB(l.name)

and L.metacategory in

(' com.gxlu.ngrm.network.DDNCircuit ',

' Com.gxlu.ngrm.network.FRCircuit ',

' Com.gxlu.ngrm.network.ATMCircuit ',

' Com.gxlu.ngrm.network.DDNOCircuit ',

' Com.gxlu.ngrm.network.FROCircuit ')

and L.id in (' 301898331 ', ' 301898335 ', ' 301908187 ', ' 301929403 ');

650) this.width=650; "title=" wps3d6f.tmp "border=" 0 "alt=" wps3d6f.tmp "src=" http://s3.51cto.com/wyfs02/M02/6E/02/ Wkiol1vxqqzytnwgaabsxx-uwmq425.jpg "245" height= "/>"

So you can use the To_single_byte function to solve.

SELECT l.id,

L.name,

To_single_byte(l.name),

Length(l.name) L1,

LENGTHB(l.name) L2,

Length(to_single_byte(l.name)) l

From Xb_link l

WHERE length(l.name) ! = LENGTHB(l.name)

and L.metacategory in

(' com.gxlu.ngrm.network.DDNCircuit ',

' Com.gxlu.ngrm.network.FRCircuit ',

' Com.gxlu.ngrm.network.ATMCircuit ',

' Com.gxlu.ngrm.network.DDNOCircuit ',

' Com.gxlu.ngrm.network.FROCircuit ')

and L.id in (' 301898331 ', ' 301898335 ', ' 301908187 ', ' 301929403 ');

650) this.width=650; "title=" wps3d70.tmp "border=" 0 "alt=" wps3d70.tmp "src=" http://s3.51cto.com/wyfs02/M00/6E/02/ Wkiol1vxqq3zediqaaditif8vqe682.jpg "" 530 "height="/>

I. 3.2 Regexp_replace--replace other characters as '

SELECT

L.id,

L.name

From Xb_link l

WHERE regexp_replace(TRIM(l.name),

' ([a-za-z0-9]| [[:p unct:]]| [[: Space:]]) ' ,

') is not NULL;

650) this.width=650; "title=" wps3d71.tmp "border=" 0 "alt=" wps3d71.tmp "src=" http://s3.51cto.com/wyfs02/M01/6E/02/ Wkiol1vxqq2az3tvaaeidflxtfg717.jpg "" 472 "height=" 106 "/>

I. 3.3 To_multi_byte Function--all full-width characters

---are all Chinese characters

SELECT l.name,

To_multi_byte(l.name)

From Xb_link l

WHERE l.name = to_multi_byte(l.name);

650) this.width=650; "title=" wps3d82.tmp "border=" 0 "alt=" wps3d82.tmp "src=" http://s3.51cto.com/wyfs02/M02/6E/02/ Wkiol1vxqq7xnupoaabjt-424pw874.jpg "" Height= "/>"

SELECT l.name,

To_multi_byte(l.name)

From Xb_link l;

650) this.width=650; "title=" wps3d83.tmp "border=" 0 "alt=" wps3d83.tmp "src=" http://s3.51cto.com/wyfs02/M00/6E/02/ Wkiol1vxqq7wqi3gaabqed7v6cw554.jpg "" "height=" 103 "/>

I. 3.4 FAQ: Does a field data contain full-width characters?

drop table AA;

CREATE TABLE AA (col varchar2(255));

SELECT * from AA for update;

650) this.width=650; "title=" wps3d84.tmp "border=" 0 "alt=" wps3d84.tmp "src=" http://s3.51cto.com/wyfs02/M01/6E/02/ Wkiol1vxqq6ikgfpaabwmk5r3v0041.jpg "" 221 "height=" 103 "/>

SELECT A.col,

To_single_byte(a.col),

Length(a.col),

LENGTHB(a.col),

Length(to_single_byte(a.col)),

LENGTHB(to_single_byte(a.col))

From AA a

WHERE (lengthb(a.col) - length(a.col)) < >

(lengthb(to_single_byte(a.col)) - length(to_single_byte (a.col)));

650) this.width=650; "title=" wps3d85.tmp "border=" 0 "alt=" wps3d85.tmp "src=" http://s3.51cto.com/wyfs02/M02/6E/02/ Wkiol1vxqq_texf9aaeegecjrmk511.jpg "" 1148 "height="/>

I. 4 SUMMARY

The method is very simple, the netizen may have other method, welcome message, for different scene processing way have many kinds, we should learn to be flexible.

I. 5 about ME

......................................................................................................................... ..................................................................

In this paper, wheat seedling, only focus on the database technology, pay more attention to the use of technology

Itpub blog:http://blog.itpub.net/26736162

This address: http://blog.itpub.net/26736162/viewspace-1688209/

This article PDF version: http://yunpan.cn/QCwUAI9bn7g7w Extract code: af2d

qq:642808185 flocculent QQ Please indicate the title of the article you are reading

Date of creation: 2015-06-05 10:00~ 2015-06-05 13:00 in forex trading center

< copyright, the article is allowed to reprint, but must be linked to the source address, otherwise investigate legal liability!>

......................................................................................................................... ..................................................................

How to determine whether a string contains Chinese characters in Oracle