One: Theory
The Oracle Intercept character (SUBSTR), which retrieves the character position (instr), when the then else End statement uses
Common functions: Substr and InStr
1.SUBSTR (String,start_position,[length]) to substring, return string
Explain:
String tuple
Start_position start position (starting from 0)
Length optional, number of substrings
For example:
substr(
"ABCDEFG"
, 0); //返回:ABCDEFG,截取所有字符
substr(
"ABCDEFG"
, 2); //返回:CDEFG,截取从C开始之后所有字符
substr(
"ABCDEFG"
, 0, 3); //返回:ABC,截取从A开始3个字符
substr(
"ABCDEFG"
, 0, 100); //返回:ABCDEFG,100虽然超出预处理的字符串最长度,但不会影响返回结果,系统按预处理字符串最大数量返回。
substr(
"ABCDEFG"
, -3); //返回:EFG,注意参数-3,为负值时表示从尾部开始算起,字符串排列位置不变。
2.INSTR (string,substring,position,ocurrence) Find string position
Explain:
String: Source string
SubString: substring to find
Position: Where to find start
Ocurrence: A substring of the first occurrence of a source string
For example:
INSTR (' CORPORATE floor ', ' or ', 3, 2), the source string is ' CORPORATE floor ', the target string is ' OR ', the starting position is 3, the position of the 2nd match is taken, and the return result is 14 '
Two: Actual test
select
substr(
‘OR:com.lcs.wc.placeholder.Placeholder:860825‘
,INSTR(
‘OR:com.lcs.wc.placeholder.Placeholder:860825‘
,
‘:‘
, 1, 2)+1,length(
‘OR:com.lcs.wc.placeholder.Placeholder:860825‘
))
,INSTR(
‘OR:com.lcs.wc.placeholder.Placeholder:860825‘
,
‘:‘
, 1, 2),
length(
‘OR:com.lcs.wc.placeholder.Placeholder:860825‘
)
From
dual;
CREATE TABLE Test (name VARCHAR2);
INSERT into test values (' ca-ca1344-a-11oct141250-d ');
INSERT into test values (' jd-jd693-a-11oct141105-d ');
Method 1:
Select substr (name,instr (name,'-', 1) +1,instr (name,'-', InStr (name,'-', 1) +1)-instr ( name,'-', 1)-1) from test;
Method 2:
Select substr (Name, '-', Name,instr) +1,instr (name,'-', ") -1-instr (name, '- ', ()) from test;
Next we'll write a second "-" to the third "-" string between the method:
Select substr (name,instr (name,'-', 2,2) +1,instr (name,'-', 2,3) -1-instr (name,'-', 2,2)) from test;
Ebs_sql_ Tip: Truncate