Oracle sql queries

Question

I am working on a small relational database for school and having trouble with a simple query. I am supposed to find all records in a table that have a field with the word 'OMG' somewhere in the text. I have tried a few other and I can't figure it out. I am getting an invalid relational operator error.

my attempts:

select * from comments where contains('omg');
select * from comments where text contains('omg');
select * from comments where about('omg');

and several more variations of the above. As you can see I am brand spanking new to SQL. text is the name of the field in the comments table.

Thanks for any help.

CONTAINS is a special keyword used with CONTEXT indexes - see docs.oracle.com/cd/B28359_01/text.111/b28303/ind.htm — Jeffrey Kemp
– Jeffrey Kemp, Commented Oct 16, 2013 at 2:36

Nir Alfasi · Accepted Answer · 2013-10-16 00:07:23Z

1

Assuming that the column name is text:

select * from comments where text like '%omg%';

The percentage % is a wild-card which means that any text can come before/after omg

Pay attention, if you don't want the results to contain words in which omg` is a substring - you might want to consider adding whitespaces:

select * from comments where text like '% omg %';

Of course that it doesn't cover cases like:

omg is at the end of the sentence (followed by a ".")
omg has "!" right afterwards
etc

edited Oct 16, 2013 at 0:07

answered Oct 16, 2013 at 0:01

Nir Alfasi

53.6k11 gold badges94 silver badges138 bronze badges

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

Comments

showdev · Accepted Answer · 2013-10-16 00:02:04Z

1

You may want to use the LIKE operator with wildcards (%):

SELECT * FROM comments WHERE text LIKE '%omg%';

answered Oct 16, 2013 at 0:02

showdev

29.3k37 gold badges61 silver badges82 bronze badges

2 Comments

Nir Alfasi Over a year ago

ans this answer is different than mine cause ?

showdev Over a year ago

Nope, not much different. We just hit it around the same time -- no harm no foul. Yours has a description of what a wildcard does. Mine has a link to LIKE documentation, so I'll leave it for reference.

Collectives™ on Stack Overflow

Oracle sql queries

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related