1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need help on substr and instr

Discussion in 'SQL PL/SQL' started by jagadekara, Apr 29, 2016.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    we have data like this...

    Select col1 from table;

    COL1
    ----------
    WINCOR,WINDOWS XP, 2HI,EMV CARD READER,1500XE, EJ MASKED
    ABC,DEFGHXP, 2HI,EMV CARD READER,1500XE, EJ MASKED

    Required output.

    REQ1 REQ2 REQ3 REQ4 REQ5 REQ6

    WINCOR WINDOWS XP 2HI EMV CARD READER 1500XE EJ MASKED

    ABC DEFGHXP 2HI EMV CARD READER 1500XE EJ MASKED

    comma position is not fixed, so we need to get comma position by using instr.

    I know we can do it by using substr and instr. I am trying but it will take huge time to me. So any help?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi,Jagadekara.

    What is your oracle vesrion ?
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    My version is

    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

    PL/SQL Release 11.1.0.7.0 - Production

    "CORE 11.1.0.7.0 Production"

    TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production

    NLSRTL Version 11.1.0.7.0 - Production
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    may be helpful...

    Code (SQL):
    WITH
        exmp_data AS
        (  
            SELECT
                'WINCOR,WINDOWS XP, 2HI,EMV CARD READER,1500XE, EJ MASKED,
    ABC,DEFGHXP, 2HI,EMV CARD READER,1500XE, EJ MASKED,
    ABC1,DEFGHXP1, 2HI,EMV CARD READER1,1500XE1, EJ MASKED1,
    ABC2,DEFGHXP2, 2HI2,EMV CARD READER2,1500XE2, EJ MASKED2,'
     col1
            FROM
                dual
        )
        ,step1 AS
        (
            SELECT
                regexp_substr(col1,'[^,]+',1,level) str,
                MOD(level-1,6) prt,
                level rn
            FROM
                exmp_data e    
            CONNECT BY          
                regexp_substr(col1,'[^,]+',1,level) IS NOT NULL
                AND
                sys_guid() IS NOT NULL
        )
        ,step2 AS
        (
            SELECT
                st1.str,          
                st1.prt,
                rank() OVER(partition BY st1.prt ORDER BY st1.rn) bkt
            FROM
                step1 st1
        )
    SELECT
    *
    FROM step2 st2
    pivot
    (
        MAX(str) req
        FOR prt IN (0,1,2,3,4,5)
    )  
     
     
    jagadekara and Sadik like this.