CODEWITHSUNDEEP
sqloracle-databaseoracle11g
BODYBOND
BODYBOND

Reputation: 127

select statement to get values between two strings in Oracle

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 ??

Upvotes: 0

Views: 981

Answers (1)

MT0
MT0

Reputation: 167962

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;

Upvotes: 2

Related Questions