Remove parenthesis out of a data value

How do I remove these parenthesis within this data value in a sql script?
I want to remove anything that has a parenthesis in the value below.
From this:
'7A120222-01(903)'

and return this:
'1U120231-01'

Also, how to create a script to remove:
From this:
'7A120222-01'

and return this:
'1U120231'

These will be used in populating 2 different columns within a table.

Thanks,
MG.
MachinegunnerAsked:
Who is Participating?
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:
Replace ant value with a paran wirth a hard-coded value?

If so:
case when instr(column_name,'(') > 0 then '1U120231' else column_name end

Not sure I understand the second one either
Replace '7A120222-01' with '1U120231'?

literally replace:
replace(column_name,'7A120222-01', '1U120231')
0
MachinegunnerAuthor Commented:
No, these won't be a hard-coded value but a value within a column like - part_nbr.
I was just using those 'hard-coded' values as an example.

Thanks
0
slightwv (䄆 Netminder) Commented:
I don't understand.

I don't see how I can turn '7A120222-01(903)' into '1U120231-01'
and  '7A120222-01' into '1U120231'

There doesn't seem to be any pattern/formula I can apply to do it.

Please provide sample data and expected results

For example:
drop table tab1 purge;
create table tab1(col1 varchar2(20));

insert into tab1 values('7A120222-01(903)');
insert into tab1 values('7A120222-01');
insert into tab1 values('Hello');
commit;

Open in new window


Then based on the sample data, what are the results you expect?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gerwin Jansen, EE MVETopic Advisor Commented:
You can use this to remove the (923) part:
select regexp_replace (your_column,'\(([[:digit:]]{3})\)') from your_table;

Open in new window

If you have the -01 part in another column:
select regexp_replace (other_column,'\-([[:digit:]]{2})$') from your_table;

Open in new window


Are your 2 examples in separate columns? Then you can combine the 2 above:
select
regexp_replace (your_column,'\(([[:digit:]]{3})\)'),
regexp_replace (other_column,'\-([[:digit:]]{2})$')
from your_table;

Open in new window

0

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
Naveen KumarProduction Manager / Application Support ManagerCommented:
'7A120222-01(903)' -->  and return this:  '1U120231-01'

 '7A120222-01' -->  and return this: '1U120231'

Can you explain your logic on how to get the output with which we can come up with the sql query.

Thanks,
0
awking00Commented:
Removing the parentheses at the end is easily accomplished using regular expressions or even a simply substring, for example, substr(yourcol,instr(yourcol,'(') - 1) Replacing a value of '7A120222-01' with '1U120231' requires a lot more description. Can you provide more sample data with multiple values plus your expected results and the criteria for thier detemination.
0
awking00Commented:
The substr example should have been -
 substr(yourcol,instr(yourcol,1, '(') - 1
0
slightwv (䄆 Netminder) Commented:
>>The substr example should have been -

FYI:  You can edit your post as long as no one has posted after you...

Given this is now three people that has pretty much said the same thing, I doubt the asker saw that post before you could have edited it...  ;)
0
awking00Commented:
slightwv, I wish I could have edited it but since this website changed, I don't seem to have that option along with many others, including the acceptance of solutions, using the bold, italics, etc, attaching files, and putting cod into snippets. I have a feeling this is all due to my outdated version of IE that I'm not allowed to change.
0
MachinegunnerAuthor Commented:
Thank you for all the replies.
I have tried the regexp_replace expression for the part_nbr replacement.  As seen below.
I had to move part data from another system to another and by using the part_nbr, I could
now update 2 other columns - document_nbr and eng_part_nbr.

Here are the updates I used:
--get rid of the partners with parenthesis... Eng_Part_Nbr column...
update part master
set eng_part_nbr = regexp_replace(part_nbr,'\(([[:digit:]]{3})\)')
where part_nbr like '%(%'
and eng_part_nbr is null;

--get rid of the eng_part_nbr from the hyphen... Document_Nbr column...
update part_master
set document_nbr = regexp_replace(eng_part_nbr,'\-([[:digit:]]{2})$')
where document_nbr is null
and eng_part_nbr is not null;
0
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
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.