And yet another piece of logic on LCF function

Pertains to Question ID: 28685124

One more piece of logic that I need with my function that returns the last company after variations of LCF. Now they want me to look for LCF and if it has a character after it then its a valid company.
Example: 'LCFY HOBOKEN LLC Y HOSPITALITY LLC'
That's a valid company but it has the LCF that we are looking for in it? Is there anyway we can look for a-z and if it is the next character then return the whole thing back as if it were a valid company?
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.

slightwv (䄆 Netminder) Commented:
First:
There was a bug in all the originals.  If part of the remaining name had a special character in it, it was removed.

Second:
I hope you want any leading or trailing spaces removed?

I'm getting better with regular expressions but this is starting to exceed my limits...  Keep going and I'll need to call in the BIG guns!!!

I might anyway.  What I came up with is sort of ugly...

If all that is cool, try this:
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 EXCH-ANGE INC' col1 FROM   dual 
union all 
select 'end with L/C/F' col1 FROM   dual 
union all 
select 'Hello' FROM   dual 
union all
select 'LCFY HOBOKEN LLC Y HOSPITALITY LLC' from dual
union all
select 'LCF BOB LCFY HOBOKEN LLC' from dual
)
SELECT
trim(
	Regexp_replace(
		Regexp_replace (Regexp_replace(' ' || col1 || ' ', 'L[^[:alnum:] ]?C[^[:alnum:] ]?F[^[:alnum:] ]?',
			       	'LCF' 
                                      , 1, 0, 
                                             'i'), 
                                            'LOST CONTENT FOR|LOST CONTRACT_FOR' 
                      , 'LCF' 
                             , 1, 0, 'i')
		, '(.*LCF[ ])(.*$)', '\2') 
)
FROM   mydata 
/ 

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
jknj72Author Commented:
Ill try it out in the morning and thanks again for all your help.

>> I hope you want any leading or trailing spaces removed?
Do you mean when getting the last company returned with spaces in front or behind the value  ? I do a trim before I return it. If not, let me know what your talking about and Ill let you know.

Don't be so hard on yourself, you definitely know your stuff and I've learned a ton from all your help.
slightwv (䄆 Netminder) Commented:
>>I do a trim before I return it. If not, let me know what your talking about and Ill let you know.

I meant the trim.  I had to add it to what I posted to 'trick' it into working.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jknj72Author Commented:
Well I think it is working but im doing additional testing.
 
I hate to ask but is there any way you could briefly explain what is going on and what some of these special characters mean and what they are dong? Sorry to be a pain

Regexp_replace(
            Regexp_replace (Regexp_replace(' ' || col1 || ' ', 'L[^[:alnum:] ]?C[^[:alnum:] ]?F[^[:alnum:] ]?',
                               'LCF'
                                      , 1, 0,
                                             'i'),
                                            'LOST CONTENT FOR|LOST CONTRACT_FOR'
                      , 'LCF'
                             , 1, 0, 'i')
            , '(.*LCF[ ])(.*$)', '\2')
slightwv (䄆 Netminder) Commented:
>>Sorry to be a pain

No problem.  It's part of the learning process.  Regular Expressions used to confuse me.  Many still do but I'm more comfortable with them these days.

>>briefly explain what is going on

Briefly?  Nope and I might get the 'official' terminologies incorrect.  I'll post how I remember them.  It may not be what the 'world' calls them.

[:alnum:]  -- is a character class for alpha-numeric characters.  There are others but I'll let you Google them...

[] is a character group.

A ^ outside of a character group is "start of line".
Inside a character group it is a NOT.

SO, [^[:alnum:] ] means: anything NOT an alpha-numeric or a space (there is a space after the alnum class)

The ? at the end means it is "optional"

SO, L[^[:alnum:] ]?  says: MATCH a letter "L" NOT followed by a alpha-numeric or a space.

Same for C and F.  The replace then replaces ALL those matches with 'LCF'.  It makes the rest of the string matching easier so you don't have to hard-code ALL possible combinations.
jknj72Author Commented:
Got it...that's actually what I was looking for an explanation on so Thanks....And everything works so thanks again!!
jknj72Author Commented:
Great job
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.