CODEWITHSUNDEEP
regexoracle-database
sam
sam

Reputation: 2616

how to get out string oracle regex

I have the following string my trying get out the 1111111 and 33333333333 with out the | character 

SELECT regexp_substr('7|1111111|2222222|33333333333|0||20140515|||false|0|0|0|0|0|','*[|]*[|][0-9]*')FROM dual

Upvotes: 0

Views: 77

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181027

Using REGEXP_REPLACE may be a bit simpler;

SELECT REGEXP_REPLACE('7|1111111|2222222|33333333333|0||20140515|||false|0|0|0|0|0|', 
                      '^([^|]*[|]){1}([^|]*).*$', '\2') FROM dual;

> 1111111

SELECT REGEXP_REPLACE('7|1111111|2222222|33333333333|0||20140515|||false|0|0|0|0|0|', 
                      '^([^|]*[|]){3}([^|]*).*$', '\2') FROM dual;

> 33333333333

You can choose column by choosing how many pipes to skip in the {1} part.

A simple SQLfiddle to test with.

A short explanation of the regexp;

([^|]+[|]){3}    -- Matches 3 groups of {optional characters}{pipe}
(\d*)            -- Matches the next digit group (the one we want)
.*               -- Matches the rest of the expression

What we want is the second paranthesized group, that is, we replace the whole string by the back reference \2.

Upvotes: 2

ThinkJet
ThinkJet

Reputation: 6745

Because "|" separators always present it's simpler to extract fields with simple substring function rather than using regular expressions.
Just find positions of corresponding separators in source string and extract content between them:

with test_data as (
  select 
    '7|1111111|2222222|33333333333|0||20140515|||false|0|0|0|0|0|ABC' as s,
    8 as field_number  -- test 1, 3, 8, 10 and 16
  from dual   
)  
select 
  field_number,
  substr(
    s, 
    decode( field_number, 
      1,1, 
      instr(s,'|',1,field_number - 1) + 1
    ),
    (
      decode( instr(s,'|',1,field_number), 
        0, length(s)+ 1, 
        instr(s,'|',1,field_number)
      )
      - 
      decode( field_number, 
        1, 1, 
        instr(s,'|',1,field_number - 1) + 1
      )
    ) 
  ) as field_value
from 
  test_data

SQLFiddle

This variant works with empty fields, non-numeric fields and so on.

Possible simplification with appending additional separators to the start and the end of the string:

with test_data as (
  select 
    ( 
      '|' ||
      '7|1111111|2222222|33333333333|0||20140515|||false|0|0|0|0|0|ABC' ||
      '|' 
    ) as s, -- additional separators appended before and after original string
    10 as field_number  -- test 1, 3, 8, 10 and 16
  from dual   
)  
select 
  field_number,
  substr(
    s,
    instr(s, '|', 1, field_number) + 1,
    (
      instr(s, '|', 1, field_number + 1)
      -
      (instr(s, '|', 1, field_number) + 1)
    )  
  ) as field_value
from 
  test_data
;

SQLFiddle

Upvotes: 0

Related Questions