CODEWITHSUNDEEP
sqloracle-databaseteradata
CodeBlooded
CodeBlooded

Reputation: 1

SQL Search using another Field as String Value

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

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! :)

Upvotes: 0

Views: 76

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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|$)')

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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.

Upvotes: 4

Related Questions