CODEWITHSUNDEEP
sqloracle-databaseoracle10goracle11g
paras2682
paras2682

Reputation: 541

Using Oracle CONTAINS Clause for 2 columns

How can I use CONTAINS clause for searching from 2 columns of a table. For eg:- lets say I have a table named CONTACT. It contains columns like FIRST_NAME, LAST_NAME, USERNAME, EMAIL etc.

Now if I want to search for a string, say 'ABCD' in the column USERNAME using CONTAINS clause, I'll write:

SELECT * 
FROM CONTACT 
WHERE (CONTAINS(USERNAME, 'ABCD', 1)> 0);

It gives me the desired result. But what If I have to search the string on 2 columns? I tried using:-

SELECT * 
FROM CONTACT 
WHERE (CONTAINS(USERNAME, 'ABCD', 1)> 0)
AND (CONTAINS(FIRST_NAME, 'ABCD', 1)> 0);

But it gives me an error. Please help me with this.

PS: Indexes on columns have been created as required for using CONTAINS clause.

Upvotes: 1

Views: 7023

Answers (2)

Anita Kulkarni
Anita Kulkarni

Reputation: 324

Actually, if you are using contains clause more than once, then you can skip the label:

SELECT * FROM CONTACT WHERE (CONTAINS(USERNAME, 'ABCD')> 0) AND (CONTAINS(FIRST_NAME, 'ABCD')> 0);

It automatically adds the labels.

Upvotes: 3

JWK
JWK

Reputation: 492

Your label is not unique, try:

SELECT * 
FROM CONTACT 
WHERE (CONTAINS(USERNAME, 'ABCD', 1)> 0)
AND (CONTAINS(FIRST_NAME, 'ABCD', 2)> 0);

Upvotes: 3

Related Questions