8 Replies Latest reply: Jan 25, 2013 4:29 AM by _Phil RSS

    Importing Part of a Column of Data

    bostonmacosx
      Hello there. Oracle 11g PLSQL question

      I'm wondering how I can import a partial amount of data from a CSV column.

      Let's say that the Data is:

      XXX.YY.ZZZZ what I want to insert though is just XXX.
      So I need to I believe take the piece of data and read it up to the first period in the name.

      I'm just stumped as how to do that.

      I think I will have a result of

      INSERT INTO TABLE (col001,col002) VALUES (some_function(XXX.YY.ZZZZ),'hello');

      So I'm just not sure what thefunction would be to do this though.

      Any help appreciated.
        • 1. Re: Importing Part of a Column of Data
          _Phil
          Use the instr() function to identify the location of the first period, and substr() to select up to that position.

          e.g.

          select substr('xxx.zzz.yyy',1, instr('xxx.zzz.yyy', '.')-1) from dual

          Edited by: _Phil on Jan 24, 2013 4:36 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
          • 2. Re: Importing Part of a Column of Data
            BluShadow
            One way...
            SQL> ed
            Wrote file afiedt.buf
            
              1* select regexp_substr('XXX.YY.ZZZZ','[^.]+',1,1) from dual
            SQL> /
            
            REG
            ---
            XXX
            Another way...
            SQL> ed
            Wrote file afiedt.buf
            
              1* select substr('XXX.YY.ZZZZ',1,instr('XXX.YY.ZZZZ','.')-1) from dual
            SQL> /
            
            SUB
            ---
            XXX
            • 3. Re: Importing Part of a Column of Data
              Robert Angel
              With instr you can find the position of your first .

              http://www.techonthenet.com/oracle/functions/instr.php

              With substr you can take the first X characters

              http://www.techonthenet.com/oracle/functions/substr.php

              Put the two together and presto; -


              substr('XXX.YY.ZZ',1,instr('XXX.YY.ZZ','.') -1)


              regards,

              Robert.

              Edited by: Robert Angel on Jan 24, 2013 8:35 AM
              • 4. Re: Importing Part of a Column of Data
                AlbertoFaenza
                Hi,

                if the number of character in the string in always with 3 characters then you can simply use SUBSTR function.
                In case the number of characters is variable and you have to take it up to the first period (assuming you always have a period in the string) then you can use SUBSTR and INSTR together
                -- case if length is fixed to 3
                WITH mydata AS
                (
                   SELECT 'XXX.YY.ZZZZ' str FROM DUAL
                )
                SELECT SUBSTR(str,1,3) str FROM mydata;
                
                STR
                ---------------
                XXX            
                
                
                -- case where length is variable
                WITH mydata AS
                (
                   SELECT 'XXX.YY.ZZZZ' str FROM DUAL UNION ALL
                   SELECT 'CC.YY.ZZZZ' str FROM DUAL UNION ALL
                   SELECT 'BBBBB.YY.ZZZZ' str FROM DUAL
                )
                SELECT SUBSTR(str,1,INSTR(str,'.')-1) str FROM mydata;
                
                STR
                ------------------------------
                XXX                           
                CC                            
                BBBBB                         
                Regards.
                Al
                • 5. Re: Importing Part of a Column of Data
                  bostonmacosx
                  Thank you for all of your help.
                  One final question.
                  I got it to work for a single string but how about if I looping through a cursor and some of the text has the period and some doesn't. If it doesn't then I get "0" in return and then the substring returns nothing.

                  thanks
                  • 6. Re: Importing Part of a Column of Data
                    Frank Kulash
                    Hi,
                    bostonmacosx wrote:
                    Thank you for all of your help.
                    One final question.
                    I got it to work for a single string but how about if I looping through a cursor and some of the text has the period and some doesn't. If it doesn't then I get "0" in return and then the substring returns nothing.
                    What do you want it to return when the string doesn't include a '.'? Do you want the whole string? Post a little sample data (CREATE TABLE and INSERT statements) and the results you want from that data.
                    Maybe you want this:
                    SELECT  SUBSTR ( str 
                                , 1
                                , INSTR ( str || '.'     -- ***** CHANGED *****
                                            , '.'
                                     ) - 1
                                )               AS str 
                    FROM    mydata;
                    Concatenting a '.' to the end of str guarantess that INSTR won't return 0. If str contains a '.', then INSTR will return the position of that dot; otherwise, INSTR will return 1 + LENGTH (str) (or 1, if str is NULL).
                    • 7. Re: Importing Part of a Column of Data
                      bostonmacosx
                      yes I just want to leave it alone.....so that it just imports it.

                      so XXX.YY.ZZZ becomes XXX
                      and AABBSS id just AABBSS
                      declare 
                        cursor emp_cursor is
                          select c001, c002,c003,c004,c005,c006,c007,c008,c009,c010,c011,c012,c013,c014,c015,c016,c017,c018,c019,c020,c021,c022,c023,c024,c025,c026,c027,c028,c029,c030
                          from apex_collections
                          where collection_name = v('P_COLLECTION_VAR');
                      BEGIN
                        FOR emp_record IN emp_cursor
                        LOOP
                          IF emp_cursor%ROWCOUNT > 1 THEN
                            INSERT INTO SLA_TIMES ("NODE_NAME",
                      "TEST_NAME",
                      "START_TIME",
                      "END_TIME",
                      "247_TOTAL_AVAIL",
                      "247_GREEN_AVAIL",
                      "247_GREEN_SECONDS",
                      "247_CLEAR_AVAIL",
                      "247_CLEAR_SECONDS",
                      "247_BLUE_AVAIL",
                      "247_BLUE_SECONDS",
                      "247_PURPLE_AVAIL",
                      "247_PURPLE_SECONDS",
                      "247_YELLOW_AVAIL",
                      "247_YELLOW_SECONDS",
                      "247_RED_AVAIL",
                      "247_RED_SECONDS",
                      "SLA_TOTAL_AVAIL",
                      "SLA_GREEN_AVAIL",
                      "SLA_GREEN_SECONDS",
                      "SLA_CLEAR_AVAIL",
                      "SLA_CLEAR_SECONDS",
                      "SLA_BLUE_AVAIL",
                      "SLA_BLUE_SECONDS",
                      "SLA_PURPLE_AVAIL",
                      "SLA_PURPLE_SECONDS",
                      "SLA_YELLOW_AVAIL",
                      "SLA_YELLOW_SECONDS",
                      "SLA_RED_AVAIL",
                      "SLA_RED_SECONDS"
                      ) VALUES (
                      emp_record.c001, 
                      emp_record.c002,
                      emp_record.c003,
                      emp_record.c004,
                      emp_record.c005,
                      emp_record.c006,
                      emp_record.c007,
                      emp_record.c008,
                      emp_record.c009,
                      emp_record.c010,
                      emp_record.c011,
                      emp_record.c012,
                      emp_record.c013,
                      emp_record.c014,
                      emp_record.c015,
                      emp_record.c016,
                      emp_record.c017,
                      emp_record.c018,
                      emp_record.c019,
                      emp_record.c020,
                      emp_record.c021,
                      emp_record.c022,
                      emp_record.c023,
                      emp_record.c024,
                      emp_record.c025,
                      emp_record.c026,
                      emp_record.c027,
                      emp_record.c028,
                      emp_record.c029,
                      emp_record.c030);
                          END IF;
                        END LOOP;
                      
                        APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => v('P_COLLECTION_VAR'));
                      
                      END;
                      SO I want c001 to be subtringed if there is a "." in it and not if there isn't.

                      Edited by: bostonmacosx on Jan 24, 2013 1:45 PM
                      • 8. Re: Importing Part of a Column of Data
                        _Phil
                        I've used something like this in the past...

                        Same as my previous code, only this time I have added a NULLIF(<str>, 0) function around the INSTR() function. Therefore, if there isn't a period in your string, the INSTR() will return 0, which in turn causes the NULLIF() function to return NULL. I've then used an NVL() to set the SUBSTR length parameter to the length of the full string.

                        SELECT substr('xxx.zzz.yyy',1, nvl(nullif(instr('xxx.zzz.yyy', '.'), 0)-1, LENGTH('xxx.zzz.yyy'))) FROM dual;
                        ----
                        xxx



                        SELECT substr('xxxyyyzzz',1, nvl(nullif(instr('xxxyyyzzz', '.'), 0)-1, LENGTH('xxxyyyzzz'))) FROM dual;
                        ----
                        xxxyyyzzz