How to intercept some characters in a string in oracle

In Oracle, you can use the Instr() and substr() functions to intercept part of the characters in the string. The syntax is "SUBSTR('String to be intercepted', interception starting position, INSTR('Required Intercepted string,'','search starting position','number of occurrences')-1) ".

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

oracle intercepts part of the string in the field

Use the Instr() and substr() functions in Oracle:

In Oracle, you can use the instr function to judge a string to determine whether it contains specified characters.

The syntax is:

instr(sourceString,destString,start,appearPosition).   
instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'）

where

• sourceString represents the source string;

• destString represents The substring to be searched for in the source string;

• start represents the starting position of the search. This parameter is optional and defaults to 1;

• appearPosition represents how many times destString appears from the source character. This parameter is also optional and defaults to 1;

If the value of start is a negative number, then it means The search is performed from right to left, but the location data is still calculated from left to right.

The return value is: the position of the found string.

For the instr function, we often use it like this: find the position of the specified substring from a string. For example:

SQL> select instr('yuechaotianyuechao','ao') position from dual;
 POSITION
 ----------
 6

Start searching from the 7th character

SQL> select instr('yuechaotianyuechao','ao', 7) position from dual;
 POSITION
 ----------
 17

Start from the 1st character and search for the second occurrence of the substring

SQL> select instr('yuechaotianyuechao','ao', 1, 2) position from dual;
 POSITION
 ----------
 17

For the instr function, We often use it like this: find the position of a specified substring in a string. For example:

SQL> select instr('abcdefgh','de') position from dual; 
POSITION 
---------- 
4

Starting from 1, d ranks fourth, so 4 is returned

SQL>select instr('abcdefghbc','bc',3) position from dual; 
POSITION 
---------- 
9

The third character starting from the 3rd character is c, so the string starting from 3 Find bc, return 9

Usage of the substr function, get the string with the specified starting position and length in the string, the default is the substring from the starting position to the end.

 substr( string, start_position, [ length ] )       substr('目标字符串',开始位置,长度)

For example:

      substr('This is a test', 6, 2)     would return 'is' 
      substr('This is a test', 6)     would return 'is a test' 
      substr('TechOnTheNet', -3, 3)     would return 'Net' 
      substr('TechOnTheNet', -6, 3)     would return 'The'select substr('Thisisatest', -4, 2) value from dual

SUBSTR() function

1. Purpose: Returns a string from a given character expression or memo field substring.

2. Grammar format:

substr(string string, int a, int b);
substr(string string, int a) ;　

Format 1:

1. string The string to be intercepted

2. a The starting position of the intercepted string (Note: When a is equal to 0 or 1, interception starts from the first bit)

3. b The length of the string to be intercepted

Format 2:

1. string The string that needs to be intercepted

2. a can be understood as intercepting all subsequent strings starting from the a-th character.

oracle intercepts the string

Intercept C3411.907w15 into the string C3411 before the dot SUBSTR ('C3411.907w15', 0, INSTR ('C3411.907w15 , '.', 1, 1) - 1)

SELECT SUBSTR ('C3411.907w15', 0, INSTR ('C3411.907w15, '.', 1, 1) - 1)
  FROM DUAL

Intercept the string 907w15 after the dot from C3411.907w15 SUBSTR ('C3411.907w15', INSTR ('C3411.907w15', '.' , 1, 1) 1)

SELECT SUBSTR ('C3411.907w15', INSTR ('C3411.907w15', '.', 1, 1)+1)
  FROM DUAL

Project application example:

Original string:

Original There are 7 "_" in the field. Now I just want to take out the string after the last "_". How to solve it?

Two steps:

The first step is to find the 7th "_" character through the Instr() function:

select instr('Q room Net Group_Brokerage Division_South China District_Shenzhen Shihua_Sales Department_Nanshan Qianhai District_Qianhai Central Area_Qianhai Fifth Branch','_', 1, 7) fdisplayname from SHENZHENJM1222.B

Or:

select instr('Qfang.com Group_Brokerage Division_South China District_Shenzhen Shihua_Business Department_Nanshan Qianhai District_Qianhai Center Area_Qianhai Wufen Branch', '_', -1, 1) fdisplayname FROM SHENZHENJM1222.B

The two have the same effect. The -1 below means starting from the right Count the starting character, 1 means to get the first "_"

The obtained result is as shown in the figure:

Step 2, use the substr() function to get the required fields:

select substr(fdisplayname,instr(fdisplayname,'_',-1 ,1) 1) fdisplayname from SHENZHENJM1222.B

fdisplayname: Represents the field name, equivalent to metastring

Focus on the introduction 1

1 means starting to intercept the first string after the target string "_"

Let's take a look at the situation of different numbers:

#select substr(

fdisplayname ,instr(fdisplayname,'_',-1,1) 1) fdisplayname from SHENZHENJM1222.B

Let’s take a look at what 1 in instr(fdisplayname,'_',-1,1) means:

# Recommended tutorial: "

Oracle Tutorial

"

The above is the detailed content of How to intercept some characters in a string in oracle. For more information, please follow other related articles on the PHP Chinese website!