Questions And Answers

More Tutorials

Oracle Regular expression

Let's say we want to replace only numbers with 2 digits: regular expression will find them with (\d\d)

SELECT REGEXP_REPLACE ('2, 5, and 10 are numbers in this example', '(\d\d)', '#')
FROM dual;

Results in:

'2, 5, and # are numbers in this example'

If I want to swap parts of the text, I use \1, \2, \3 to call for the matched strings:

SELECT REGEXP_REPLACE ('swap around 10 in that one ', '(.*)(\d\d )(.*)', '\3\2\1\3')
FROM dual;


SUBSTR retrieves part of a string by indicating the starting position and the number of characters to extract




To count from the end of the string, SUBSTR accepts a negative number as the second parameter, e.g.

SELECT SUBSTR('abcdefg',-4,2) FROM DUAL;



To get the last character in a string: SUBSTR(mystring,-1,1)


In this page (written and validated by ) you learned about Oracle Regular expression . What's Next? If you are interested in completing Oracle tutorial, your next topic will be learning about: Oracle IF THEN ELSE Statement.

Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue:

Share On:

Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using, you agree to have read and accepted our terms of use, cookies and privacy policy.