Compare between strings in query statement in Oracle

Question

I have this table:

id         value
AA000001   500
AA000002   1000
AA000003   1500
AA000004   2000
AA000005   2500
AA000006   3000
AA000007   3500

The type of id is a string and I use below sql statement to query my record:

SELECT sum(value), max(value), min(value) FROM my_table WHERE id BETWEEN 'AA000001' AND 'AA000007'

And it's works as expected. But I wonder if there are any exceptions in this query?

I'm using Oracle 10g Release 2 and 11g Release 2.

Thanks in advances.

Gordon Linoff · Accepted Answer · 2013-08-13 02:34:49Z

3

I'm not exactly sure what you mean by "exceptions". Your query is:

SELECT sum(value), max(value), min(value)
FROM my_table
WHERE id BETWEEN 'AA000001' AND 'AA000007';

It will match anything between these values. In addition to what is in your table, it will match, say, 'AA000003ZYW243'. You are safe that all matches will start with 'AA00000'. The next character will be between '1' and '7', and then anything can follow that (unless that character is a '7').

answered Aug 13, 2013 at 2:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

PhatHV Over a year ago

In my table the length of column ID is fixed. And I'm very happy for this sentence "You are safe that all matches will start with 'AA00000'. The next character will be between '1' and '7". Thanks for your answer.

Collectives™ on Stack Overflow

Compare between strings in query statement in Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related