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!

Split Column Value

Discussion in 'SQL PL/SQL' started by rudolf_r, Jun 10, 2014.

  1. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Hi All,

    I have a COLUMN that contains data like "346/1/3", "346/1/4", which I want to split into another column that have "1,3", and "1,4" which is "portion 3 of portion 1 of number 346" and "portion 4 of portion 1 of number 346". In some instances there is no portion of a portion so those will be "whole" numbers.

    I have tried with for example using "regexp_substr(columnname,'[^ ]+', 1, 3)" in the sql select but I'm not getting this correctly split.

    Thanks.
    Rudolf
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have the right idea, just the wrong character in the regexp_substr() parameters:


    Code (SQL):

    SQL> col yup_dta_dist format a45
    SQL>
    SQL> CREATE TABLE yupninski(
      2        yup_id  NUMBER,
      3        yup_nm  varchar2(25),
      4        yup_dta varchar2(40));


    TABLE created.


    SQL>
    SQL> BEGIN
      2        FOR i IN 1..100 loop
      3         INSERT INTO yupninski(yup_id, yup_nm, yup_dta)
      4         VALUES(i, 'Blopo'||i, '346/1/'||i);
      5        END loop;
      6  
      7        INSERT INTO yupninski(yup_id, yup_nm, yup_dta)
      8        VALUES(0, 'Blopo0', '346/1');
      9  
     10        commit;
     11  END;
     12  /


    PL/SQL PROCEDURE successfully completed.


    SQL>

    SQL>
    SQL> SELECT yup_id, yup_nm, CASE WHEN instr(yup_dta, '/',1,2) > 0 THEN 'Portion '||regexp_substr(yup_dta,'[^/]+', 1, 3)||' of portion '|| regexp_substr(yup_dta,'[^/]+', 1, 2)||' of number '|| regexp_substr(yup_dta,'[^/]+', 1, 1) ELSE 'Number '||regexp_substr(yup_dta,'[^/]+', 1, 1) END yup_dta_dist
      2  FROM yupninski;


        YUP_ID YUP_NM                    YUP_DTA_DIST
    ---------- ------------------------- ---------------------------------------------
             1 Blopo1                    Portion 1 OF portion 1 OF NUMBER 346
             2 Blopo2                    Portion 2 OF portion 1 OF NUMBER 346
             3 Blopo3                    Portion 3 OF portion 1 OF NUMBER 346
             4 Blopo4                    Portion 4 OF portion 1 OF NUMBER 346
             5 Blopo5                    Portion 5 OF portion 1 OF NUMBER 346
             6 Blopo6                    Portion 6 OF portion 1 OF NUMBER 346
             7 Blopo7                    Portion 7 OF portion 1 OF NUMBER 346
             8 Blopo8                    Portion 8 OF portion 1 OF NUMBER 346
             9 Blopo9                    Portion 9 OF portion 1 OF NUMBER 346
            10 Blopo10                   Portion 10 OF portion 1 OF NUMBER 346
            11 Blopo11                   Portion 11 OF portion 1 OF NUMBER 346


        YUP_ID YUP_NM                    YUP_DTA_DIST
    ---------- ------------------------- ---------------------------------------------
            12 Blopo12                   Portion 12 OF portion 1 OF NUMBER 346
            13 Blopo13                   Portion 13 OF portion 1 OF NUMBER 346
            14 Blopo14                   Portion 14 OF portion 1 OF NUMBER 346
            15 Blopo15                   Portion 15 OF portion 1 OF NUMBER 346
            16 Blopo16                   Portion 16 OF portion 1 OF NUMBER 346
            17 Blopo17                   Portion 17 OF portion 1 OF NUMBER 346
            18 Blopo18                   Portion 18 OF portion 1 OF NUMBER 346
            19 Blopo19                   Portion 19 OF portion 1 OF NUMBER 346
            20 Blopo20                   Portion 20 OF portion 1 OF NUMBER 346
            21 Blopo21                   Portion 21 OF portion 1 OF NUMBER 346
            22 Blopo22                   Portion 22 OF portion 1 OF NUMBER 346


        YUP_ID YUP_NM                    YUP_DTA_DIST
    ---------- ------------------------- ---------------------------------------------
            23 Blopo23                   Portion 23 OF portion 1 OF NUMBER 346
            24 Blopo24                   Portion 24 OF portion 1 OF NUMBER 346
            25 Blopo25                   Portion 25 OF portion 1 OF NUMBER 346
            26 Blopo26                   Portion 26 OF portion 1 OF NUMBER 346
            27 Blopo27                   Portion 27 OF portion 1 OF NUMBER 346
            28 Blopo28                   Portion 28 OF portion 1 OF NUMBER 346
            29 Blopo29                   Portion 29 OF portion 1 OF NUMBER 346
            30 Blopo30                   Portion 30 OF portion 1 OF NUMBER 346
            31 Blopo31                   Portion 31 OF portion 1 OF NUMBER 346
            32 Blopo32                   Portion 32 OF portion 1 OF NUMBER 346
            33 Blopo33                   Portion 33 OF portion 1 OF NUMBER 346


        YUP_ID YUP_NM                    YUP_DTA_DIST
    ---------- ------------------------- ---------------------------------------------
            34 Blopo34                   Portion 34 OF portion 1 OF NUMBER 346
            35 Blopo35                   Portion 35 OF portion 1 OF NUMBER 346
            36 Blopo36                   Portion 36 OF portion 1 OF NUMBER 346
            37 Blopo37                   Portion 37 OF portion 1 OF NUMBER 346
            38 Blopo38                   Portion 38 OF portion 1 OF NUMBER 346
            39 Blopo39                   Portion 39 OF portion 1 OF NUMBER 346
            40 Blopo40                   Portion 40 OF portion 1 OF NUMBER 346
            41 Blopo41                   Portion 41 OF portion 1 OF NUMBER 346
            42 Blopo42                   Portion 42 OF portion 1 OF NUMBER 346
            43 Blopo43                   Portion 43 OF portion 1 OF NUMBER 346
            44 Blopo44                   Portion 44 OF portion 1 OF NUMBER 346


        YUP_ID YUP_NM                    YUP_DTA_DIST
    ---------- ------------------------- ---------------------------------------------
            45 Blopo45                   Portion 45 OF portion 1 OF NUMBER 346
            46 Blopo46                   Portion 46 OF portion 1 OF NUMBER 346
            47 Blopo47                   Portion 47 OF portion 1 OF NUMBER 346
            48 Blopo48                   Portion 48 OF portion 1 OF NUMBER 346
            49 Blopo49                   Portion 49 OF portion 1 OF NUMBER 346
            50 Blopo50                   Portion 50 OF portion 1 OF NUMBER 346
            51 Blopo51                   Portion 51 OF portion 1 OF NUMBER 346
            52 Blopo52                   Portion 52 OF portion 1 OF NUMBER 346
            53 Blopo53                   Portion 53 OF portion 1 OF NUMBER 346
            54 Blopo54                   Portion 54 OF portion 1 OF NUMBER 346
            55 Blopo55                   Portion 55 OF portion 1 OF NUMBER 346


        YUP_ID YUP_NM                    YUP_DTA_DIST
    ---------- ------------------------- ---------------------------------------------
            56 Blopo56                   Portion 56 OF portion 1 OF NUMBER 346
            57 Blopo57                   Portion 57 OF portion 1 OF NUMBER 346
            58 Blopo58                   Portion 58 OF portion 1 OF NUMBER 346
            59 Blopo59                   Portion 59 OF portion 1 OF NUMBER 346
            60 Blopo60                   Portion 60 OF portion 1 OF NUMBER 346
            61 Blopo61                   Portion 61 OF portion 1 OF NUMBER 346
            62 Blopo62                   Portion 62 OF portion 1 OF NUMBER 346
            63 Blopo63                   Portion 63 OF portion 1 OF NUMBER 346
            64 Blopo64                   Portion 64 OF portion 1 OF NUMBER 346
            65 Blopo65                   Portion 65 OF portion 1 OF NUMBER 346
            66 Blopo66                   Portion 66 OF portion 1 OF NUMBER 346


        YUP_ID YUP_NM                    YUP_DTA_DIST
    ---------- ------------------------- ---------------------------------------------
            67 Blopo67                   Portion 67 OF portion 1 OF NUMBER 346
            68 Blopo68                   Portion 68 OF portion 1 OF NUMBER 346
            69 Blopo69                   Portion 69 OF portion 1 OF NUMBER 346
            70 Blopo70                   Portion 70 OF portion 1 OF NUMBER 346
            71 Blopo71                   Portion 71 OF portion 1 OF NUMBER 346
            72 Blopo72                   Portion 72 OF portion 1 OF NUMBER 346
            73 Blopo73                   Portion 73 OF portion 1 OF NUMBER 346
            74 Blopo74                   Portion 74 OF portion 1 OF NUMBER 346
            75 Blopo75                   Portion 75 OF portion 1 OF NUMBER 346
            76 Blopo76                   Portion 76 OF portion 1 OF NUMBER 346
            77 Blopo77                   Portion 77 OF portion 1 OF NUMBER 346


        YUP_ID YUP_NM                    YUP_DTA_DIST
    ---------- ------------------------- ---------------------------------------------
            78 Blopo78                   Portion 78 OF portion 1 OF NUMBER 346
            79 Blopo79                   Portion 79 OF portion 1 OF NUMBER 346
            80 Blopo80                   Portion 80 OF portion 1 OF NUMBER 346
            81 Blopo81                   Portion 81 OF portion 1 OF NUMBER 346
            82 Blopo82                   Portion 82 OF portion 1 OF NUMBER 346
            83 Blopo83                   Portion 83 OF portion 1 OF NUMBER 346
            84 Blopo84                   Portion 84 OF portion 1 OF NUMBER 346
            85 Blopo85                   Portion 85 OF portion 1 OF NUMBER 346
            86 Blopo86                   Portion 86 OF portion 1 OF NUMBER 346
            87 Blopo87                   Portion 87 OF portion 1 OF NUMBER 346
            88 Blopo88                   Portion 88 OF portion 1 OF NUMBER 346


        YUP_ID YUP_NM                    YUP_DTA_DIST
    ---------- ------------------------- ---------------------------------------------
            89 Blopo89                   Portion 89 OF portion 1 OF NUMBER 346
            90 Blopo90                   Portion 90 OF portion 1 OF NUMBER 346
            91 Blopo91                   Portion 91 OF portion 1 OF NUMBER 346
            92 Blopo92                   Portion 92 OF portion 1 OF NUMBER 346
            93 Blopo93                   Portion 93 OF portion 1 OF NUMBER 346
            94 Blopo94                   Portion 94 OF portion 1 OF NUMBER 346
            95 Blopo95                   Portion 95 OF portion 1 OF NUMBER 346
            96 Blopo96                   Portion 96 OF portion 1 OF NUMBER 346
            97 Blopo97                   Portion 97 OF portion 1 OF NUMBER 346
            98 Blopo98                   Portion 98 OF portion 1 OF NUMBER 346
            99 Blopo99                   Portion 99 OF portion 1 OF NUMBER 346


        YUP_ID YUP_NM                    YUP_DTA_DIST
    ---------- ------------------------- ---------------------------------------------
           100 Blopo100                  Portion 100 OF portion 1 OF NUMBER 346
             0 Blopo0                    NUMBER 346


    101 ROWS selected.


    SQL>
     

    Replacing the ' ' with a '/' (the character you want to split the data on) fixed the issue you were having.
     
  3. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Thanks, David. This works perfectly.