I want to capture a string after a few digits are not fixed, however, oracle SQL need how to write,,,
Such as 16 _4253116201_3600 * 1 * 4 after the string may take six of seven after eight this SQL how to write

CodePudding user response:

String before half if solid, then the goal of intercepting the starting position of the string is certain, not sure the length of the intercept, but the length can be calculated according to the length and the location of the intercept,

SUBSTR intercept substring

Grammar: SUBSTR (string, a [b])

Function: return to the starting from a position of a substring of string b characters long. The position of the first character is a 1. If a is 0, then it is considered to start with the first character. If it is a positive number, return character calculated from left to right. If b is negative, then the return of characters from a string calculated from right to left at the end of the beginning. If b doesn't exist, then it will be the default Settings for the entire string. If less than 1 b, then will return NULL. If a or b use floating point Numbers, then the value will be for first before processing an integer.

Use location: procedural statements and SQL statements,

CodePudding user response:

Calculation can be upstairs, you can also use regular

With TMP as
(select '16 _4253116201_3600 * 1 * 4' as p_str from dual)
The select regexp_substr (TMP) p_str,
'[^ _] +'
1,
Regexp_count (TMP) p_str, '[^ _] +))
The from TMP.

CodePudding user response:

refer to the second floor stelf response:

You can write, do not need to use count

 WITH TMP AS 
(SELECT '16 _4253116201_3600 * 1 * 4' AS P_STR FROM DUAL) 
The SELECT REGEXP_SUBSTR (TMP) P_STR, '[^ _] + $', 1, 1) the FROM TMP. 

CodePudding user response:

refer to the second floor stelf response:

calculation can be upstairs, you can also use regular

With TMP as
(select '16 _4253116201_3600 * 1 * 4' as p_str from dual)
The select regexp_substr (TMP) p_str,
'[^ _] +'
1,
Regexp_count (TMP) p_str, '[^ _] +))
The from TMP.

Thank you

CodePudding user response:

reference chengccy reply: 3/f

Quote: refer to the second floor stelf response:

You can write, do not need to use count

 WITH TMP AS 
(SELECT '16 _4253116201_3600 * 1 * 4' AS P_STR FROM DUAL) 
The SELECT REGEXP_SUBSTR (TMP) P_STR, '[^ _] + $', 1, 1) the FROM TMP. 

Thank you ~! The results I try

CodePudding user response:

Cooperate with written INSTR

CodePudding user response:

Reverse the interception (after the interception of several), direct use can be negative
Select substr (16 _4253116201_3600 * 1 * 4-6) from dual;