CODEWITHSUNDEEP
sqloracle-database
Randy
Randy

Reputation: 11

SQL LIKE query not working

I'm trying to run the following query against an Oracle DB, but the query is returning 0 records:

select * from TABLE 
where upper(FIELD) like '%SEE COMMENT%'

I know this field contains many records with 'See Comment" in it. For example, here is one of the records:

=if(and(Robust_Mean>=20,Robust_Mean<=70),.03*(Robust_Mean+29),
if(Robust_Mean>70,.083*(Robust_Mean^.9),"See Comment"))

I am guessing that the quotation marks in the field are messing the query up, but im not sure how to get around it. Any suggestions?

Upvotes: 0

Views: 1539

Answers (2)

Randy
Randy

Reputation: 11

Just realized there were two similarly named fields in this table, and I was choosing the wrong one.

Upvotes: 1

Harrison
Harrison

Reputation: 9090

This works for me:

create table testLike (aCol varchar2(500) );

INSERT INTO TESTLIKE VALUES('abc');
insert into testLike values('=if(and(Robust_Mean>=20,Robust_Mean<=70),.03*(Robust_Mean+29),
if(Robust_Mean>70,.083*(Robust_Mean^.9),"See Comment"))');


SELECT * 
  FROM TESTLIKE TL 
 WHERE upper(tl.acol) like '%SEE COMMENT%';

can you recreate?

edit: in your query try this:

select * from TABLE 
WHERE UPPER(FIELD) = '=if(and(Robust_Mean>=20,Robust_Mean<=70),.03*(Robust_Mean+29),
if(Robust_Mean>70,.083*(Robust_Mean^.9),"See Comment"))';

see if that comes up with any results

Upvotes: 1

Related Questions