SUBSTR Intercept string in Oracle and intercept with InStr range matching string position

Source: Internet
Author: User

1:update table Name set column name = SUBSTR (column name, INSTR (column name, ' match character ', "+1") Where condition like '%*% '

2:select SUBSTR (column name, INSTR (column name, ', ', +1) as D from table name T where condition like '%-% ';

Example: Select INSTR (' orc+001 ', ' + ', ' a ') from dual
The return is "4" if the string has no matching character and returns "0".
The format of the Instr:instr method is
INSTR (source string, target string, starting position, matching ordinal)


On 2: +1 is because the intercepted position is starting from the matching character , so the next one to start interception, no length interception, if not +1 will be retained, intercepted

Gets the string substr (string, start_position, [length]) that specifies the starting position and length in the string
Such as:
SUBSTR (' This was a test ', 6, 2) would return ' is '
SUBSTR (' This was 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 the result is Te

Select substr (' Emros ', -3,1) value from dual the result is R

Substr intercepts strings in Oracle and intercepts InStr range-matching string positions

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.