Return range of Oracle PL / SQL strings

Question

Return range of Oracle PL / SQL strings

Navigation

#1 by (1 votes)

1

Good morning! I need to return the value inside a string that is between a range of | in the select below I can get the value from the first | . The expected result is: 83,1

SELECT SUBSTR('1410,00|83,1|39,29|1410m|', 
       INSTR('1410,00|83,1|39,29|1410m|', '|') + 1, 
       LENGTH('1410,00|83,1|39,29|1410m|')) TTR
  FROM DUAL;

sql oracle query pl-sql

asked by anonymous 19.07.2018 / 13:16

1 answer

Store array inside Cookie Append lines to existing file

score 1 · Accepted Answer

You can use a regular expression combined with the REGEXP_SUBSTR :

select regexp_substr('1410,00|83,1|39,29|1410m|','[^\|]+',1,2) from dual

The function will apply to the regular expression [^\|]+ that will fetch through a pipe (the "+" at the end means "one or more occurrences"), from position 1, second occurrence. See how you want the second value "83.2", the final parameter of the function was "2", in case "3" will return "39,29" and so on.

Here's the fiddle running: link