Oracle Special Character Processing search and replacement

Oracle Special Character Processing search and replacement

A colleague encountered a special character matching Replacement problem a few days ago. Here is a summary. What should we do if our data contains special characters such as line breaks and carriage returns?

1, Use of Replace functions

Here we need to use two functionsChr,Ascii.

First useAsciiFunction to identify special charactersAsciiValue, or directly viewAsciiTable. For example, we know that the line feed is10; Press enter is13.

Replace wrap with null : Replace(T. v1,Chr(10),'')

2. Use of the regexp_replace Function

Of course, the best way is to replace it with a regular expression. Enter Line feed Spaces all belong to null characters. We can use[: Apace:]To match :Regexp_replace (t. v1,'[[: Space:]','')

We can also use this to query columns with special characters: (Here we think numbers and letters are all)

SelectT. *, t. rowid FromT_table t  WhereRegexp_replace (t. v1,'[[: Digit:] | [[: alpha:]','')IsNotNull;

3, Combined Use

In particular, when we want to process other input characters (such as Russian characters), we can use them together to first query the characters that are given by numbers, letters, spaces, and punctuation marks.

Select* FromPrm. ent_inter_tariff_iddout_bak t  WhereRegexp_replace (t. countries,'[[: Digit:] | [[: alpha:] | [[: space:] | [[: punct:]','')IsNotNull;

We can see that the result contains 'czech Republic Mobile Mobil release om'Where'Authorization'It should be'K', So we can queryAsciiValue

SelectAscii('Authorization')FromDual; ----- 53434(Result) Replace(Countries,Chr(53434),'K')

In this way, the replacement of other characters can be mistakenly entered.

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12