Avatar of William Peck
William PeckFlag for United States of America asked on

Need help with Oracle Regexp_substr

Need an Oracle Regular Expression to pull out FRENCH from this pattern

RECEIVED A LANGUAGE STUDY MINOR IN FRENCH, which is embedded in this sample string. Assume the string ends in a period(.)


Sample full string.

ATTENDED UGA 1ST SEM/FALL 1997-98 AS AN EXCHANGE STUDENT.
RECEIVED A LANGUAGE STUDY MINOR IN FRENCH. CAPSTONE SCHOLAR PROJECT - 'THE EFFECTS OF FIBER REINFORCED PLASTIC COMPOSITES ON THE DUCTILITY OF RETROFITTED CONCRETE BEAMS'


I tried but can't follow the REGEXP_SUBSTR pages such as this - it's all too cryptic to me, and I hardly use this.







* Oracle PL/SQLOracle DatabaseSQL

Avatar of undefined
Last Comment
William Peck

8/22/2022 - Mon
Sean Stuber

what is the rule for determining that substring?

Kent Olsen

It appears that you're looking for the string 'FRENCH.'.

If so, the LIKE predicate is fine

WHERE text LIKE '%FRENCH.%'


EDIT:  Sorry -- looked at this backwards.  :)
Sean Stuber

Assuming you want the 2nd line ending in a period, try this...

select regexp_substr(yourcolumn,'^[^.]+\.',1,2,'m') from yourtable

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
William Peck

@Kent Olsen - you remind me again I need to make my questions a little more clear ... I got the %wild card part, Sean finally figured out what I really meant ... but thank you.

@Sean Stuber - yes on # 2 - - - you figured out what I was trying to say ... and whoa - that worked!!!!! I would have never figured that out from the tech page, Thank you!!!
ASKER
William Peck

@Sean - you've helped me many times over the past 17 years - wow. One in particular was an Oracle analytic function from way back, and then 7 years later I needed to get the syntax again, and I was able to do a google search and found the original EE post ... thx!