Additional help requested for question ID: 28683216

I don't know how to link this question to the original I asked but the ID: 28683216

 I checked this morning and I need some further assistance. I guess I should have stated more examples of the data I need to cleanse. I tried it with the string below and it didn't work?
I think If I knew how to incorporate any new characters, if that's the problem, that I need to look for maybe that would be best for me?

Im gonna keep testing to try and narrow it down....

pass this to function
'COMPANYA SERVICES INC L/C/F     COMPANYB TIRE EXCHANGE INC'

get back
'COMPANYA SERVICES INC LCF     COMPANYB TIRE EXCHANGE INC'

need returned
'COMPANYB TIRE EXCHANGE INC'

THANKS FOR THE HELP!!!!
jknj72Asked:
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.

jknj72Author Commented:
Just to put a little more emphasis on the logic to help understand better.

The data is structured in a way that if we use a company, lets say Company A, and then another company is used the data entry people will put some sort of version of 'LCF' in front of the old company and then enter the new company. So it would look like 'Company A L/C/F Company B' so I would need Company B returned from the function. And this can go on and on and on so I would always need the company after the last variation of LCF

The real issue is all the different variations of the LCF that is entered to look for.

So far we are looking for
"LCF", "-LCF", "L/C/F", "L/C/F,", "L.C/F/,", "L/C/F/", "L.C.F", "L.C.F.", "-L/C/F", "L/C/F-", "L-C-F", "LCF/", "L/C/F:", "LCF:", ".L/C/F", "L\C\F", "L\C\F:", ".L\C\F", ".L/C/F", "L C F", "LOST CONTENT FOR", "LABOR CONTRACTOR FOR:"

Sorry for not mentioning this in the first question

THANKS
slightwv (䄆 Netminder) Commented:
I see the flaw in my original solution.  It required TWO LCF entries.  Let me see if I (or another Expert) can figure out a solution for 0 to many.
jknj72Author Commented:
Ok Thanks slight
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.

johnsoneSenior Oracle DBACommented:
Actually, this slight change to the original posted seems to work.  Test has 0, 1 and 2 occurrences.
WITH mydata 
     AS (SELECT 
     'MyText lost content for Company A LCF COMPANY B L/C/F COMPANY  C' col1 
         FROM   dual 
         UNION ALL 
         SELECT 'COMPANYA SERVICES INC L/C/F     COMPANYB TIRE EXCHANGE INC' 
                col1 
         FROM   dual 
         UNION ALL 
         SELECT 'HEllo' 
         FROM   dual) 
SELECT Regexp_replace(Regexp_replace (Regexp_replace(col1, '[^[:alnum:] ]', NULL 
                                      , 1, 0, 
                                             'i'), 
                                            'LOST CONTENT FOR|LOST CONTRACT_FOR' 
                      , 'LCF' 
                             , 1, 0, 'i'), '(.*LCF )(.*$)', '\2') 
FROM   mydata 

/ 

Open in new window

I am by no means an expert with regular expressions, however, this seemed to work.  Rather that looking for the second occurrence specifically, this seems to get the last one.

If you want to remove the leading spaces, you can trim it, or use another replace to get rid of multiple spaces.

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
jknj72Author Commented:
Johnson, the value passed in can have any number of LCF variations, literally....I want to return just the one value back so although what your function is doing does return the last value, The problem is that it will be just the one value sent to the function and that one value is the one that needs to be evaluated and then the last part of the string returned.

I hope that helps

Thanks
JK
slightwv (䄆 Netminder) Commented:
>>The problem is that it will be just the one value sent to the function and that one value is the one that needs to be evaluated and then the last part of the string returned.


I don't understand this part of the statement.

It looked like johnsone's post did what you needed so I quit working on it.  What isn't it doing?
sdstuberCommented:
>>>  can have any number of LCF variations, literally..

you mean it can look like this?

"A LCF  B  L.C.F C  L/C/F  D"

and if so you want "D"  ?
jknj72Author Commented:
Yes that is correct.....
jknj72Author Commented:
Slight
>>I don't understand this part of the statement.
>> It looked like johnsone's post did what you needed so I quit working on it.  What isn't it doing?

I am actually double checking what he sent right now. Don't see the need for the UNION ALL so I have to do it without and will let you guys know...
slightwv (䄆 Netminder) Commented:
>>Don't see the need for the UNION ALL

You don't need that.

That is just a CTE (WITH) to set up the test data.  That isn't part of what you need.

You just need the SELECT after that replacing the table and column with your actual tables.
jknj72Author Commented:
It actually is working Johnson just let me do some additional testing and I will let you know...Thanks
jknj72Author Commented:
Yeah johnsone I think you nailed it. I took out the UNION ALLs and tested it and it works great. Thank you for your help
jknj72Author Commented:
Thank you very much
jknj72Author Commented:
Thanks Slight
johnsoneSenior Oracle DBACommented:
All I did was a slight modification of what slightwv posted on the first question.

The UNION ALLs were just there to generate the test data.  They aren't part of the processing.
jknj72Author Commented:
I understand now that slight cleared it up for me...Im a little slow ;)
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.