CODEWITHSUNDEEP
sqloracle-database
camdixon
camdixon

Reputation: 892

SQL error ORA-00920: invalid relational operator, and I don't know why

I'm going to use regex_count and regex_like in one statement, but first I need to get count to work. I don't understand why it is growing an error when these are valid columns, correct amount of parenthesis and I have the apostrophes. Also using Oracle SQL Developer.

select employee_name, LTRIM(street)
from employee
where 
regexp_count( street, '[[:digit:]]' );

ORA-00920: invalid relational operator 00920. 00000 - "invalid relational operator" *Cause:
*Action: Error at Line: 4 Column: 37

What I'm really trying to do is remove any starting street numbers from the street and return the result set. I was going to use this below, and then figure out how to feed it into a substring function to get the result set i'm looking for. Any suggestions?

select employee_name, LTRIM(street)
from employee
where 
regexp_like ( street, '[[:digit:]]', 1, regexp_count( street, '[[:digit:]]' ) );

Upvotes: 0

Views: 2020

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

REGEXP_COUNT() returns an integer, not a boolean. So, you need a comparison:

select employee_name, LTRIM(street)
from employee
where regexp_count( street, '[[:digit:]]' ) > 1;

If the comparison is just > 0, then you don't need to count the number of patterns. Just use regexp_like().

If you want to remove leading digits, use regexp_substr():

select regexp_substr(street, '[^[:digit:]].*')

Upvotes: 2

Related Questions