CODEWITHSUNDEEP
mysqlsqloracle-databasecharacterbetween
Drake
Drake

Reputation: 95

Oracle Between Operator for Character Values

I have the following Table which has sequence number and Station codes. I want to bring list of stations between two stations.

If i do 

select Station Code 
from Table1 
where Station Code BETWEEN 'BWI' and 'PHL'

It should give me stations based on the sequence like this -> (BWI,BAL,WIL,PHL).

Whereas it is giving my the following stations -> (BWI,PHL,NWK,NYP).

Please let me know who do i bring the desired values? I have added the sample Data in Table1 below.

Table1 

Sequence Number Station Code
    1           WAS
    2           BWI
    3           BAL
    4           WIL
    5           PHL
    6           NWK
    7           NYP

Upvotes: 1

Views: 186

Answers (4)

siva krishna
siva krishna

Reputation: 122

select * from table1

where sequence between

(select sequence_number from table where station code= 'BWI')

and

(select sequence_number from table where station code= 'PHL');

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

If you don't know the ordering of the stations, you can use:

select t1.Code 
from Table1 t1 cross join
     (select min(number) as minn, max(number) maxn
      from table1
      where code in ('BWI' and 'PHL')
     tt
where t1.number between minn and maxnn;

Upvotes: 1

MT0
MT0

Reputation: 167962

Since it's tagged with Oracle, here's an Oracle solution which does not need multiple table scans:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table1 ( Sequence_Number, Station_Code ) AS
          SELECT 1, 'WAS' FROM DUAL
UNION ALL SELECT 2, 'BWI' FROM DUAL
UNION ALL SELECT 3, 'BAL' FROM DUAL
UNION ALL SELECT 4, 'WIL' FROM DUAL
UNION ALL SELECT 5, 'PHL' FROM DUAL
UNION ALL SELECT 6, 'NWK' FROM DUAL
UNION ALL SELECT 7, 'NYP' FROM DUAL

Query 1:

SELECT *
FROM   table1
START WITH Station_Code = 'BWI'
CONNECT BY PRIOR Sequence_Number  = Sequence_Number - 1
AND        PRIOR Station_Code <> 'PHL'

Results:

| SEQUENCE_NUMBER | STATION_CODE |
|-----------------|--------------|
|               2 |          BWI |
|               3 |          BAL |
|               4 |          WIL |
|               5 |          PHL |

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

The problem is 

'BWI' and 'PHL'

is returning all rows where code is between those as string comparasion.

You want 

SELECT *
FROM Table1
WHERE Sequence Number 
    between (SELECT Sequence Number FROM Table1 WHERE Station Code ='BAL')
    and (SELECT Sequence Number FROM Table1 WHERE Station Code ='PHL')

Upvotes: 0

Related Questions