Forum Stats

  • 3,872,140 Users
  • 2,266,394 Discussions
  • 7,911,063 Comments

Discussions

Extract portion of strings based on the positions

KVB
KVB Member Posts: 194 Blue Ribbon
edited Jun 3, 2013 9:01AM in SQL & PL/SQL
Hi

My requirement is simple.I get the below data from the source file.

My data looks like this.Sometimes I get a space,one tab space,two tab space.It's ambiguous.

FIRSTWORD SECONDWORD THIRDWORD FOURTH (o/p=FIRSTWORD SECONDWORD THIRDWORD)
FIRSTWORD SECONDWORD THIRD WORD(o/p=FIRSTWORD SECONDWORD THIRD )


If it is something fixed,then I can write using INSTR and SUBSTR and it worked.But how we can handle multiple cases all at a time.

Thanks in advance
KVB
Tagged:

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 42,540 Red Diamond
    Answer ✓
    Perhaps something like this...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (select 'FIRSTWORD SECONDWORD  THIRDWORD FOURTHWORD' as txt from dual union all
      2             select 'FIRSTWORD       SECONDWORD THIRDWORD       FOURTHWORD' from dual)
      3  --
      4  -- end of data containing single or multiple spaces and/or tabs
      5  --
      6  select regexp_substr(txt, '[^ ]+', 1, 1) as first
      7        ,regexp_substr(txt, '[^ ]+', 1, 2) as second
      8        ,regexp_substr(txt, '[^ ]+', 1, 3) as third
      9        ,regexp_substr(txt, '[^ ]+', 1, 4) as fourth
     10* from (select regexp_replace(txt,'[[:space:]+]',' ') as txt from t)
    SQL> /
    
    FIRST           SECOND          THIRD           FOURTH
    --------------- --------------- --------------- ---------------
    FIRSTWORD       SECONDWORD      THIRDWORD       FOURTHWORD
    FIRSTWORD       SECONDWORD      THIRDWORD       FOURTHWORD

Answers

  • dariyoosh
    dariyoosh Member Posts: 348
    edited Jun 3, 2013 8:59AM
    Hello,


    If it is possible to search the data in the loaded file based on a given pattern, then you can use REGEXP_SUBSTR

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions150.htm#SQLRF06303


    Regards,
    Dariyoosh
    dariyoosh
  • BluShadow
    BluShadow Member, Moderator Posts: 42,540 Red Diamond
    Answer ✓
    Perhaps something like this...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (select 'FIRSTWORD SECONDWORD  THIRDWORD FOURTHWORD' as txt from dual union all
      2             select 'FIRSTWORD       SECONDWORD THIRDWORD       FOURTHWORD' from dual)
      3  --
      4  -- end of data containing single or multiple spaces and/or tabs
      5  --
      6  select regexp_substr(txt, '[^ ]+', 1, 1) as first
      7        ,regexp_substr(txt, '[^ ]+', 1, 2) as second
      8        ,regexp_substr(txt, '[^ ]+', 1, 3) as third
      9        ,regexp_substr(txt, '[^ ]+', 1, 4) as fourth
     10* from (select regexp_replace(txt,'[[:space:]+]',' ') as txt from t)
    SQL> /
    
    FIRST           SECOND          THIRD           FOURTH
    --------------- --------------- --------------- ---------------
    FIRSTWORD       SECONDWORD      THIRDWORD       FOURTHWORD
    FIRSTWORD       SECONDWORD      THIRDWORD       FOURTHWORD
  • BluShadow
    BluShadow Member, Moderator Posts: 42,540 Red Diamond
    Or just...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (select 'FIRSTWORD SECONDWORD  THIRDWORD FOURTHWORD' as txt from dual union all
      2             select 'FIRSTWORD       SECONDWORD THIRDWORD       FOURTHWORD' from dual)
      3  --
      4  -- end of data containing single or multiple spaces and/or tabs
      5  --
      6  select regexp_substr(txt, '[^[:space:]]+', 1, 1) as first
      7        ,regexp_substr(txt, '[^[:space:]]+', 1, 2) as second
      8        ,regexp_substr(txt, '[^[:space:]]+', 1, 3) as third
      9        ,regexp_substr(txt, '[^[:space:]]+', 1, 4) as fourth
     10* from t
    SQL> /
    
    FIRST           SECOND          THIRD           FOURTH
    --------------- --------------- --------------- ---------------
    FIRSTWORD       SECONDWORD      THIRDWORD       FOURTHWORD
    FIRSTWORD       SECONDWORD      THIRDWORD       FOURTHWORD
This discussion has been closed.