Issue while using between operator in oracle SQL

Question

where location between 'G1' and 'G4'.

While using this in the end of my query the result showing is :-

G11
G12
G13
G14
G2
G3
G1
G4

I only want locations as G1,G2,G3,G4 and does not need G11,G12 etc. Thanks for your suggestions in advance.

Because your field is a string type and G2 comes after G1X. Your solution depends on the data you have in this field, are there other characters than G? — Tedo G.
– Tedo G., Commented Sep 16, 2016 at 6:41
I understand that G2 comes after G1X but is there any alternative to prevent this. No there is no other characters than G — Dip
– Dip, Commented Sep 16, 2016 at 6:44
You'd need to parse the field and the strings. If we assume that all strings always begin with a G and otherwise have only numeric characters, where substr(location,2,length(location)) between substr('G1',2,length('G1')) and substr('G4',2,length('G4')) would work. You'd probably need a function-based index to make that perform reasonably. — Justin Cave
– Justin Cave, Commented Sep 16, 2016 at 6:47
Thanks for you suggestion but there are other locations also in which there is no numeric value and have only characters whose length is also not fixed (some are 5 char long.some are 6 char long). — Dip
– Dip, Commented Sep 16, 2016 at 6:56

ScaisEdge · Accepted Answer · 2016-09-16 07:31:29Z

1

For filter query based on a range of string (adn not other filter are useful) a proper way is use a in clause eg:

select * from my_table
where my_column in ('G1', G2, 'G3', G4, 'GX5');

answered Sep 16, 2016 at 7:31

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

Comments

Jack · Accepted Answer · 2016-09-16 09:25:00Z

0

Use below where clause :

where substr(Col_name,2,length(Col_name)) between 'G1' and 'G4'

answered Sep 16, 2016 at 9:25

Jack

19311 bronze badges