Ebs_sql_ Tip: Truncate

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