CODEWITHSUNDEEP
sqloracle-database
Bigeyes
Bigeyes

Reputation: 1666

Select table value by range

In my table I have the primary key id. It is a two byte varchar type. If the id is from 00 to 89 then it returns a set of data, otherwise returns different data. My query in the stored procedure is

BEGIN
select * from MyTable where (id like '0%' or
                             id like '1%' or
                             id like '2%' or
                             id like '3%' or
                             id like '4%' or
                             id like '5%' or
                             id like '6%' or
                             id like '7%' or
                             id like '8%') 
                             and status = 'active'
union all
select * from MyTable where id like '9%' and status='inactive'
END

My question is how can I improve it? May I convert the string to number then using >89 or <90?

Upvotes: 2

Views: 54

Answers (2)

psur
psur

Reputation: 4519

You can also use regular expressions:

select * from MyTable
where REGEXP_LIKE(id, '^[0-8][0-9]') and status='active'
or REGEXP_LIKE(id, '^9[0-9]') and status='inactive'

Upvotes: 5

William Robertson
William Robertson

Reputation: 15991

How about:

select * from mytable
where  ( id <  '9' and status = 'active' )
or     ( id >= '9' and status = 'inactive' )

Upvotes: 1

Related Questions