asked on

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

slightwv (䄆 Netminder)

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 Kumar

>>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***]

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.

Steve A

ASKER

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'

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve A

ASKER

Awesome! Thanks, will try it out.