CODEWITHSUNDEEP
sqlstringoracle-database
Raja sekar
Raja sekar

Reputation: 79

Get first value and last value

i have below record like '8|12|53|123|97' and i need to find the range of values between 8 to 97, so that i need the number 8 and 97.

Upvotes: 0

Views: 166

Answers (2)

APC
APC

Reputation: 146229

Here is a solution which will work for any string which has at least one pipe.

with cte as (
  select '8|12|53|123|97' str from dual
  )
, rng as (  
select to_number(substr(str, 1, instr(str, '|')-1)) as token_1
      ,to_number(substr(str, instr(str, '|', -1)+1)) as token_2
from cte )
select token_1 + level - 1 as tkn
from rng
connect by level <= (token_2 - token_1) + 1
/

The first subquery is just your test data. The second subquery identifies the first number (token_1) and the last number (token_2) in the string. It uses substr() and instr() just because they are faster than regex. instr() with a negative offset finds the last occurence of the search argument.

The main query generates a range of numbers from the bounds of the rng subquery. Not sure if that's in your requirement (depends on what you mean by "range of values between").

Because this model is not in First Normal Form you are exposed to data quality issues. The query will not produce results if the first or last tokens are not numeric, or there's only one token or the separator is not a pipe.

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You can use REGEXP_SUBSTR as following:

SQL> SELECT
  2      REGEXP_SUBSTR('8|12|53|123|97', '^[0-9]+') FIRSTVAL,
  3      REGEXP_SUBSTR('8|12|53|123|97', '[0-9]+$') LASTVAL
  4  FROM
  5      DUAL;

FIRSTVAL   LASTVAL
---------- ----------
8          97

SQL>
  • ^ matches the beginning of a string.
  • $ matches the end of a string.

Cheers!!

Upvotes: 2

Related Questions