SQL Search using another Field as String Value

Question

I have an idea for a SQL query but I'm not sure if this is even possible. Let's imagine I have a table with 3 columns:

IDNUMBER, PARAGRAPH, ANIMAL

IDNUMBER is just a key identifier number.
PARAGRAPH is always going to have a big block of text, and sometimes it will mention an animal in the paragraph.
ANIMAL will always have a set small value like "Dog" or "Monkey".

Is there a way, for each IDNUMBER, to take the value in the Animal column and use that value for a string search of the corresponding Paragraph column? I'm not sure if I would need to assign the Animal value to some sort of string variable for each loop? This is much more of conceptual question at this point because I'm not sure if this is even possible in SQL, but something like:

SELECT
    idnumber, paragraph, animal
FROM 
    zootable
WHERE 
    paragraph NOT LIKE ('%currentAnimalValue%')

Sorry this one is so hand-wavy, I'm just looking for a sanity check on this one. Thanks for any help/wisdom you can provide! :)

I work mostly in Teradata and Oracle SQL Developer, so either of those would be good. — CodeBlooded
– CodeBlooded, Commented Dec 8, 2016 at 21:37
I agree with the below answer. I just wanted to add that you need to be careful with wildcards. For instance if you had a paragraph that had the word pants in it and the animal was ants. — SQLChao
– SQLChao, Commented Dec 8, 2016 at 21:45
I suggest you change the schema, one table for animals, one for paragraphs — Keith John Hutchison
– Keith John Hutchison, Commented Dec 9, 2016 at 0:48

Gordon Linoff · Accepted Answer · 2016-12-08 21:37:52Z

4

Do you mean something like this?

select t.*
from t
where t.paragraph not like '%' || t.animal || '%' ;

Some databases use concat(), +, or & for string concatenation.

answered Dec 8, 2016 at 21:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

CodeBlooded Over a year ago

I am amazed at how quickly you guys cracked that! Thank you so much this is getting added to the Favorites library!! :)

Vamsi Prabhala · Accepted Answer · 2016-12-08 22:09:46Z

2

Gordon's answer may produce some incorrect matches.

Eg: 'panda' like '%pandas%' returns True. 'ant' like '%pant%' returns True

To avoid this you should use regexp (in Oracle) to consider word boundaries (assuming paragraph column is space-separated).

For matches use:

select * from t 
where regexp_like(paragraph,'(^|\s)'||animal||'(\s|$)')

For non-matches use

select * from t 
where not regexp_like(paragraph,'(^|\s)'||animal||'(\s|$)')

edited Dec 8, 2016 at 22:09

answered Dec 8, 2016 at 21:54

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Collectives™ on Stack Overflow

SQL Search using another Field as String Value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related