Oracle string processing practices

Background 1:

Personnel Information table, which contains the email_address field. The values in the field are in the following format:

Name1@test.com.cn or name2/test/COM/CN

Name of the target user.

The string processing function is as follows:

Code 1:

Select email_address
--, Instr (email_address, '@', 1, 1)
--, Decode (instr (email_address, '@'), 0, 'unadded @', substr (email_address, instr (email_address, '@'), length (email_address ))) judgment
, Replace (email_address
, Substr (
Email_address
, Decode (
Decode (
Instr (email_address ,'@')
, 0
, Instr (email_address ,'@')
, 0
)
, 0
, Instr (email_address ,'/')
)
, Length (email_address) + 1-Decode (
Decode (
Instr (email_address ,'@')
, 0
, Instr (email_address, '@'), 0
)
, 0
, Instr (email_address ,'/')
)
)
)
--, Decode (instr (email_address, '@'), 0, instr (email_address, '@'), 0), 0, instr (email_address ,'/'))
From ontact

-- Complex judgments are mainly used in decode determination. The score starts with '@' or '/'

 

Process 2:

Use regexp_replace

Select email_address, regexp_replace (email_address, '(@ |/) [A-Za-Z./@] *') from ca_contact