Oracle SQL, Search for string in one column that contains string from another column

Question

Currently have this table, and need to be able to find Sentences that contain the name of a person

ID         FIRSTNAME       LASTNAME        EMAIL                         PHRASE
--------  --------------- ------------     ---------------------------   ---------------------------------------------------------------          
1          John            Smith           [email protected]              I'm the same as other people.
2          Sarah           Lane            [email protected]          I'm different from other people
3          Allen           Borazt          [email protected]              I guess I need another job.
4          Mike            Himmy           [email protected]                  We have a great time. John Smith really enjoys his time out.
5          Jillian         Carters         [email protected]             What happened to the car? 
6          Steven          Oarts           [email protected]             Just chillin' over in the pool
7          Ronald          Donalds         [email protected]                 Just in case you were wondering
8          Nancy           Arist           [email protected]              What I really want is Paul Blarts movie.
9          Paul            Blart           [email protected]               I think Nancy Arist hair color is cool.

Right now my query looks like this but it returns "No Rows Selected".

SELECT T.*
FROM Table T
WHERE T.Phrase Like '%' || T.FirstName || '%';

My desired result would look like this.

ID         FIRSTNAME       LASTNAME        EMAIL                         PHRASE
--------  --------------- ------------     ---------------------------   ------------------------------------------------------------------------------------------------------------------------               
4          Mike            Himmy           [email protected]                  We have a great time. John Smith really enjoys his time out.
8          Nancy           Arist           [email protected]              What I really want is Paul Blarts movie.
9          Paul            Blart           [email protected]               I think Nancy Arist hair color is cool.

I don't understand your expected output. You may look into using full text search. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 12, 2018 at 0:21
So basically finding any phrase that contains the firstname or lastname of a person — Adam
– Adam, Commented Apr 12, 2018 at 0:22
Sorry i was gonna post this with random table names, but thought it might be easier to read if i put data, just edited it — Adam
– Adam, Commented Apr 12, 2018 at 0:24

jose_bacoy · Accepted Answer · 2018-04-12 01:15:35Z

1

Do a sub query and use exists to check if that fname or lname is found in phrase column.

SELECT A.*
FROM Table1 A
WHERE EXISTS (
          SELECT * 
           FROM Table1 B
           WHERE B.Phrase Like '%' || A.FirstName || '%'
           OR  B.Phrase Like '%' || A.LastName  || '%')

edited Apr 12, 2018 at 1:15

answered Apr 12, 2018 at 0:31

jose_bacoy

12.7k1 gold badge25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adam Over a year ago

Phrase was defined in the same table, its all in one. Looking online it said I may need to use a subquery, but doing your solution still resulted in "No Rows Selected"

jose_bacoy Over a year ago

i didnt scroll to the right so dod not see the column phrase. i updated my answer

Adam Over a year ago

Nice!, Thanks so much this worked, just added a trim over the name parts and got my query

Gordon Linoff · Accepted Answer · 2018-04-12 00:51:09Z

1

Hmmmm. The problem is that you are looking only at the values within a single row. I think you want:

select t.*
from table t
where exists (select 1
              from table t2
              where t.Phrase Like '%' || t2.FirstName || '%'
             );

answered Apr 12, 2018 at 0:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

shrek · Accepted Answer · 2018-04-12 00:41:54Z

0

Adding a query to ignore case sensitivity

SELECT T1.*
FROM Table T1, Table T2
WHERE upper(T1.Phrase) Like '%' || upper(T2.FirstName) || '%'
OR upper(T1.Phrase) Like '%' || upper(T2.LastName) || '%';

answered Apr 12, 2018 at 0:41

shrek

8876 silver badges12 bronze badges

Collectives™ on Stack Overflow

Oracle SQL, Search for string in one column that contains string from another column

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related