select statement to get values between two strings in Oracle

Question

I have table with two VARCHAR2 columns:

trs_no trs_name
------ --------
JV1    ddd
 .      .
 .      .
 .      .
JV580  deee

I want to select for example all trs_no between JV280 and JV320

I tried the normal between but didn't work because I found JV29 in the results

So I tried also to get the length of the JV280 and JV320 in between it worked but it failed when the length is different.

How can I fix this ??

MT0 · Accepted Answer · 2017-03-29 10:26:16Z

2

Split the trs_no into a two-character string prefix and the numeric postfix and compare them seperately:

SELECT *
FROM   your_table
WHERE  SUBSTR( trs_no, 1, 2 ) = 'JV'
AND    TO_NUMBER( SUBSTR( trs_no, 3 ) ) BETWEEN 280 AND 320

If trs_no can have different sized string prefixes then you could use a regular expression:

SELECT *
FROM   your_table
WHERE  REGEXP_SUBSTR( trs_no, '^(\D+)(\d+)$', 1, 1, NULL, 1 ) = 'JV'
AND    TO_NUMBER( REGEXP_SUBSTR( trs_no, '^(\D+)(\d+)$', 1, 1, NULL, 2 ) )
         BETWEEN 280 AND 320;

edited Mar 29, 2017 at 10:26

answered Mar 29, 2017 at 10:19

MT0

173k12 gold badges70 silver badges136 bronze badges

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

2 Comments

BODYBOND Over a year ago

Is there any way more easier ?

MT0 Over a year ago

@AbdelrhmanHesham You could add couple of virtual columns to the table and then just select from them in the form.

Collectives™ on Stack Overflow

select statement to get values between two strings in Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related