Find a string pattern and replace it with another value

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?
Something like:
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.

Much appreciated.

Database - Oracle 11g
MachinegunnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I don't think I understand:  Are you wanting hard-coded values like you asked or something more generic?

If you want the hard-coded values, it is pretty straight forward with the same sort of if statements.
Pawan KumarDatabase ExpertCommented:
>>In other words, there are many patterns to check on.  Can this be done in Oracle?
Yes it is possible.

>>You can use like below if you need this in a select statement. Using CASE Statement. The name of the new column will be document_nbr. I dont think function is required for this kind of requirement.

SELECT
othercolumns,
CASE WHEN machine_nbr = '10113-0019-200(611)' then '10113-0019'
     WHEN machine_nbr = '13A10283-1(20)' then '13A10283-1'
     WHEN machine_nbr = '1MTD120275-03(109)' then 'TD120275'
       ELSE 'SomeValue'
END as document_nbr
FROM yourTableName
Alex [***Alex140181***]Software DeveloperCommented:
I strongly advise NOT to go for a static solution like Pawan suggested using hardcoded literals! That is just BAD PRACTICE, nothing more! Find one or more pattern that suit your needs and wrap it up in some package based function using REGEXP_REPLACE.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MachinegunnerAuthor Commented:
Thanks for the replies,
These wouldn't be specific values but like a generic pattern to look for.  So if a specific nbr is passed in and fits a pattern,
then just return part of that nbr.
--pseudo-code (not syntax correct):
create function get_document_nbr(machine_nbr varchar2(100))
document_nbr varchar2(100);
case when machine_nbr = 'xxxx-xxxx-xx(xxx)' then 'xxxx-xxxx'
         when machine_nbr = 'xxxxxxxxxx-x(xx)' then 'xxxxxxxxxx-x'
         when machine_nbr = '1MTDxxxxxx-xx(xxx)' then use 'TDxxxxxx'
       else machine_nbr
end
return document_nbr

select get_document_nbr('10113-0019-200(611)' from dual;   --returns this value - '10113-0019'
select get_document_nbr('13A10283-1(20)' from dual;             --returns this value - '13A10283-1'
select get_document_nbr('1MTD120275-03(109)' from dual;   --returns this value - 'TD120275'
slightwv (䄆 Netminder) Commented:
This can probably be cleaned up a little but try this:
create or replace function myfunc(p_val in varchar2) return varchar2
is
begin
	return
		case
			when regexp_like(p_val,'[0-9]{5}-[0-9]{4}-[0-9]{3}\([0-9]{3}\)') then substr(p_val,1,10)
			when regexp_like(p_val,'[[:alnum:]]{1,10}-[0-9]\([0-9]{2}\)') then regexp_substr(p_val,'[^(]*')
			when p_val like '1MTD%' then regexp_substr(substr(p_val,3),'[^-]*')
    	else p_val
		end;
end;
/

show errors

select myfunc('10113-0019-200(611)') from dual;
--returns this value - '10113-0019'
select myfunc('13A10283-1(20)') from dual;
--returns this value - '13A10283-1'
select myfunc('1MTD120275-03(109)') from dual;
--returns this value - 'TD120275' 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MachinegunnerAuthor Commented:
Awesome!  Thanks, will try it out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.