Home » SQL & PL/SQL » SQL & PL/SQL » Regexp_like (Oracle 12c, Win 10)
Regexp_like [message #683599] Fri, 05 February 2021 14:32 Go to next message
rajivn786
Messages: 153
Registered: January 2010
Senior Member
Hi,

I am having some difficulty in writing regexp. I need to find those that match "you" in a string, but it's picking "your" as well.


select 1 from dual
where regexp_like('Your Mother',
                           '(^|[^A-Z]+)' || 'You' || '([^A-Z]+|$)');
The code should return 1 if it has "you" and not "your" but it's returning 1. I am new to regexp. Can anyone advise?
Re: Regexp_like [message #683600 is a reply to message #683599] Fri, 05 February 2021 14:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67813
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"where substr('Your Mother',1,3) = 'You'"?

Re: Regexp_like [message #683601 is a reply to message #683599] Fri, 05 February 2021 14:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67813
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I am new to regexp

And maybe the solution is the same one than in your previous topic in 2018.
And as Bill advised you in an other of your topics:

Bill B wrote on Tue, 14 June 2016 22:55
By the way, there is a very nice and free application to learn regular expressions and to build the expression strings. I used it to learn regular expressions. it is called expresso.

http://www.ultrapico.com/Expresso.htm

Razz
Re: Regexp_like [message #683602 is a reply to message #683600] Fri, 05 February 2021 14:46 Go to previous messageGo to next message
rajivn786
Messages: 153
Registered: January 2010
Senior Member
Unfortunately I added only just 1 simple one . My mistake. We are not sure of the position of "you".
It can be anywhere like below

Some of the data I had were :


Father's Parents
Father Father's Parents You
Father Father's Parents You Siblings 
Father Father's Parents Mother's Parents Siblings
Children Father Mother's You Parents Siblings
Children Mother's Parents Siblings You
Children Father Father's Parents You Mother's Parents Siblings 

Re: Regexp_like [message #683603 is a reply to message #683602] Fri, 05 February 2021 14:58 Go to previous messageGo to next message
Michel Cadot
Messages: 67813
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The answer is in my previous post. Smile

Re: Regexp_like [message #683604 is a reply to message #683603] Fri, 05 February 2021 15:21 Go to previous messageGo to next message
rajivn786
Messages: 153
Registered: January 2010
Senior Member
I think this works only if first 3 letters are "you"

COnsider this table and output .


Drop table a;
Create table a (cd varchar2(100));

Insert into a values ('Father''s Parents');
Insert into a values ('Father Father''s Parents You');
Insert into a values ('Father Father''s Parents You Siblings'); 
Insert into a values ('Your Father Father''s Parents Mother''s Parents Siblings');
Insert into a values ('Children Father Mother''s You Parents Siblings');
Insert into a values ('Children Mother''s Parents Siblings You');
Insert into a values ('Children Father Father''s Parents Your Mother''s Parents Siblings');

O/p should be :

Father Father''s Parents You
Father Father''s Parents You Siblings
Children Father Mother''s You Parents Siblings
Children Mother''s Parents Siblings You

Re: Regexp_like [message #683605 is a reply to message #683604] Sat, 06 February 2021 00:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67813
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This post.

Re: Regexp_like [message #683606 is a reply to message #683605] Sat, 06 February 2021 01:25 Go to previous messageGo to next message
Littlefoot
Messages: 21664
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something like this, perhaps?

SQL> select * from a
  2  where regexp_like(cd, '^(.*\s+)?you(\s+.*)?$', 'i');

CD
-------------------------------------------------------------------
Father Father's Parents You
Father Father's Parents You Siblings
Children Father Mother's You Parents Siblings
Children Mother's Parents Siblings You

SQL>
Re: Regexp_like [message #683607 is a reply to message #683606] Sat, 06 February 2021 01:43 Go to previous message
Michel Cadot
Messages: 67813
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not exactly:
SQL> select 1 from dual where regexp_like('to you, to me','^(.*\s+)?you(\s+.*)?$', 'i');

no rows selected
Complete solution in in previous topic.
Previous Topic: old hash value is used to identify same sql and Plan hash value is to identify sql plans?
Next Topic: Parse Json using Oracle SQL
Goto Forum:
  


Current Time: Sat Apr 17 02:32:51 CDT 2021