CODEWITHSUNDEEP
sqloracle-databasesql-like
connollyc4
connollyc4

Reputation: 155

Oracle like and between used together

I thought this would have been an easy google search but couldn't find any solutions. Is there a way to use the like and between together in a query?

Example

REASON_CODES

A00 VMC B10
A00 RTD B19
.
.
.
A99 RNT B40

I am trying to write a query like:

Select count(*) from table_1 where REASON_CODES like between '%A10%' and '%A25%'

Is there a solution to do this? I was reading "convert" may do the trick but I had no luck.

Thanks

Upvotes: 3

Views: 4239

Answers (3)

MT0
MT0

Reputation: 168741

Oracle Setup:

CREATE TABLE TABLE_NAME ( REASON_CODES ) AS
SELECT 'A00 VMC B10' FROM DUAL UNION ALL
SELECT 'A00 RTD B19' FROM DUAL UNION ALL
SELECT 'A09 RTD B19' FROM DUAL UNION ALL
SELECT 'ASD A10 B19' FROM DUAL UNION ALL
SELECT 'XYZ A20 RTD' FROM DUAL UNION ALL
SELECT 'ABC XYZ A25' FROM DUAL UNION ALL
SELECT 'A26 RTD B19' FROM DUAL UNION ALL
SELECT 'A99 RNT B40' FROM DUAL;

Query:

SELECT *
FROM   TABLE_NAME
WHERE  REGEXP_SUBSTR( REASON_CODES, 'A\d{2}' ) BETWEEN 'A10' AND 'A25';

Output:

REASON_CODES
------------
ASD A10 B19  
XYZ A20 RTD  
ABC XYZ A25

Upvotes: 1

Barmar
Barmar

Reputation: 782693

If you're just trying to match the beginning of the REASON_CODE strings, you can do:

SELECT COUNT(*)
FROM table_1
WHERE REASON_CODE >= 'A10' AND REASON_CODE < 'A26'

This is equivalent to scaisEdge's answer, but it can take advantage of an index on the REASON_CODE column, which cannot be used if you first call SUBSTR().

You have to use >= and < because BETWEEN includes both endpoints, and you want to match everything up to, but not including A26. You can't use BETWEEN 'A10' AND 'A25' because strings beginning with A25 and having additional characters are higher than that.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

You can use substring 

  Select count(*) from table_1 
  where   substr(reason_codes, 1,3) between 'A10' and 'A25';

Upvotes: 1

Related Questions