I would like to create a function that would take a string and return part of the string based on a certain pattern.
If it satisfies a string pattern, then it should just return part of the desired string.
Such as these maching part patterns, where I need to return part of the string and goes into a document_nbr column.
if machine_nbr = '10113-0019-200(611)' then documen_nbr = '10113-0019'
if machine_nbr = '13A10283-1(20)' then documen_nbr = '13A10283-1'
if machine_nbr = '1MTD120275-03(109) then document_nbr = 'TD120275', where this takes the document out of the middle of part.
In other words, there are many patterns to check on. Can this be done in Oracle?
if pattern = 'xxxx-xxxx-xx(xxx)' then use 'xxxx-xxxx'
if pattern = 'xxxxxxxxxx-x(xx)' then use 'xxxxxxxxxx-x'
if pattern = '1MTDxxxxxx-xx(xxx)' then use 'TDxxxxxx'
Again, just some patterns as an example as there are many.
Database - Oracle 11g