Shulou(Shulou.com)06/01 Report--

substr function: intercepts strings

Syntax: SUBSTR(string, start,[length])

string: Indicates the source string, i.e. the string to be truncated.

Start: Start position, start searching from 1. If start is negative, it starts at the end of the string.

length: Optional, indicating the length of the truncated string.

Examples:

SELECT SUBSTR('Hello SQL! ', 1) FROM dual --intercepts all strings and returns'Hello SQL!' 'SELECT SUBSTR('Hello SQL! ', 2) FROM dual --Start with the second character and truncate to the end. Return 'hello SQL! 'SELECT SUBSTR('Hello SQL! ', -4) FROM dual --Start with the last four characters and truncate to the end. Return to 'SQL! 'SELECT SUBSTR('Hello SQL! ', 3, 6) FROM dual --Truncate 6 characters from the 3rd character. Return 'llo SQ'SELECT SUBSTR ('Hello SQL! ', -4, 3) FROM dual--Truncate 3 characters from the last 4 characters. Return to 'SQL'

instr function: Returns the position of a substring in the source string

Syntax: INSTR(string, child_string,[start],[show_time])

string: Indicates the source string.

child_string: The substring, i.e. the string you want to find.

start: Optional, starting position, default starts from 1. If negative, search from right to left.

show_time: Optional, indicating the number of times a substring appears in the source string. The default is the first time. If it is negative, an error will be reported.

Examples:

--Indicates to find the first occurrence of substring '_' from the first character in source string 'city_company_staff' SELECT INSTR ('city_company_staff','_') FROM dual --returns 5--indicates that the first occurrence of the substring'_'is found starting at the 5th character in the source string'city_company_staff'. SELECT INSTR ('city_company_staff','_', 5) FROM dual --returns 5--indicates that the first occurrence of the substring'_'is found starting at the 5th character in the source string'city_company_staff'. SELECT INSTR ('city_company_staff','_', 5, 1) FROM dual --returns 5--indicates to look for the second occurrence of substring'_'starting at character 3 in source string'city_company_staff'SELECT INSTR ('city_company_staff','_', 3, 2) FROM dual --returns 13--start argument is-1, retrieves from right to left, finds the first occurrence of '_' string in source string SELECT INSTR ('city_company_staff','_',-1, 1) FROM dual --returns 13--start argument is-6, search from right to left, find the second occurrence of the '_' string in the source string SELECT INSTR ('city_company_staff','_',-6, 2) FROM dual --returns 5 substr function with instr function to intercept string

Existing requirements: data query processing requires "splitting" code

Code naming rules are similar: City_Affiliated Company_Employee Position_Employee Name

Among them, the length of the city, company, position and surname strings is not fixed. Since the length of the strings is not fixed, it is impossible to achieve the requirements only by using the substr function. It is necessary to coordinate with the instr function to locate the position of the character '_', and then use the substr function to intercept. See examples below for details.

The table data are as follows:

SOURCE_CODE BJ_BAIDU_CEO_LY SH_BOKE_MANAGER_LWX HRB_WM_CASHIER_OYZY

Get City:

SELECT SUBSTR (SOURCE_CODE, 1, INSTR (SOURCE_CODE, '_', 1, 1) - 1) AS CITYFROM TABLE_CODE_TEST

Results:

BJ

SH

HRB

Explanation: here intercept the source string SOURCE_CODE, starting from the first character, because the code length representing the city is not fixed, we can not determine how many characters to intercept, so use the instr function to determine the position of the first '_' character, and then determine how many strings to intercept for each SOURCE_CODE.

So why subtract 1?

This is because INSTR (SOURCE_CODE, '_', 1, 1) gets the position of the first occurrence of the'_'character in the source string, and subtracting 1 gives the number of CITY characters.

Get Company:

SELECT SUBSTR ( SOURCE_CODE, INSTR (SOURCE_CODE, '_', 1, 1) + 1, INSTR (SOURCE_CODE, '_', 1, 2) - INSTR (SOURCE_CODE, '_', 1, 1)-1 ) AS COMPANYFROM TABLE_CODE_TEST

Results:

COMPANY

BOKE

WM

Explanation: intercept the source string, starting from (the first '_' occurrence position +1), the number of interceptions is: the second '_' occurrence position minus the first '_' occurrence position, at this time there is an extra underscore '_', and then subtract 1 to obtain the character string representing the company.

Get Name:

SELECT SUBSTR (SOURCE_CODE, INSTR (SOURCE_CODE, '_', 1, 3) + 1) AS STF_NAMEFROM TABLE_CODE_TEST

Results:

LY

LWZ

OYZY

Interpretation: Truncate the source string starting at ('_'3rd occurrence +1) and ending at the end.

---------------------

summary

The above is a summary of the common methods of string interception in Oracle introduced by Xiaobian. I hope it will help you. If you have any questions, please leave a message to me. Xiaobian will reply to you in time. Thank you very much for your support!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.