Oracle Between Operator for Character Values

Question

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

i added Table one in table form i dont know why it came as a line. I hope you guys understand that its a two column table with 7 rows of Data — Drake
– Drake, Commented Aug 25, 2015 at 22:25
I just edit for you :). You need add 4 spaces before each line to show in code format — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Aug 25, 2015 at 22:27

Gordon Linoff · Accepted Answer · 2015-08-25 22:40:28Z

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;

Thank you all , I was able to get the required result based on the solution you guys have proposed

Juan Carlos Oropeza · Accepted Answer · 2015-08-25 22:30:26Z

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')

MT0 · Accepted Answer · 2015-08-25 22:40:21Z

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 |

siva krishna · Accepted Answer · 2015-08-25 22:54:12Z

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');

Collectives™ on Stack Overflow

Oracle Between Operator for Character Values

4 Answers 4

1 Comment

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Related