14 Replies Latest reply: Dec 10, 2012 8:25 AM by AlbertoFaenza RSS

    Any Function Starts with a String and End with a String

    951418
      Hi,

      Is ther any function whch will chek for the string starts with and the string that ends with.

      E.g

      v_data

      c237z121206
      h237z121206
      a237z121206
      c238z121207
      h238z121207
      a238z121207
      c239z121210
      h239z121210
      a239z121210

      v_current_date := to_char(sysdate, 'YYMMDD');

      I want to get the string that ends with 121210 i.e instr(v_data, v_current_date) this wll bring the first occurence of 121210,

      and starts with 'a'


      So that i can get data 'a239z121210' and not 'C'

      Is there any function that can use to chekc the First and last string.


      I also dont want to use the 3rd occurence of the v_current_Date

      Please help
        • 1. Re: Any Function Starts with a String and End with a String
          Purvesh K
          Do you want this?
          with data as
          (
          select 'h239z121210' col from dual union all
          select 'a239z121210' col from dual
          )
          select *
            from data
           where regexp_like(col, '^a.*?121210$');
          COL         
          ----------- 
          a239z121210 
          • 2. Re: Any Function Starts with a String and End with a String
            vijayrsehgal-Oracle
            Hope the below helps.
            with data as (
            select 'c237z121206' col1 from dual
            union all
            select 'h237z121206' from dual
            union all
            select 'a237z121206' from dual
            union all
            select 'c238z121207' from dual
            union all
            select 'h238z121207' from dual
            union all
            select 'a238z121207' from dual
            union all
            select 'c239z121210' from dual
            union all
            select 'h239z121210' from dual
            union all
            select 'a239z121210' from dual
            union all
            select 'a239z121210121210' from dual
            )
            select col1 from data  where col1 like 'a%' and regexp_count(col1,'121210',1,'i') =1
            • 3. Re: Any Function Starts with a String and End with a String
              951418
              Thanks.. but can i use this in PL/SQL block with If condition?
              • 4. Re: Any Function Starts with a String and End with a String
                vijayrsehgal-Oracle
                Please clarify what are intend to do using pl/sql?
                • 5. Re: Any Function Starts with a String and End with a String
                  951418
                  This is the .txt file. Extracting the whole contecnt of the file and storing it in Clob Datatype v_data with UTL_HTTP package

                  c237z121206
                  h237z121206
                  a237z121206
                  c238z121207
                  h238z121207
                  a238z121207
                  c239z121210
                  h239z121210
                  a239z121210


                  Once i get all the data in v_data

                  I need to check for today sysdate in the YYMMDD format and the string starting from 'a'

                  So that i can retrieve the 'a239z121210'
                  • 6. Re: Any Function Starts with a String and End with a String
                    vijayrsehgal-Oracle
                    why not use external tables and extract just the records that match your criteria using sql, no pl/sql, no storage in database?
                    • 7. Re: Any Function Starts with a String and End with a String
                      951418
                      if PL/SQL table used.. am i able to use the reg_exp like function from pl.SL table?
                      • 8. Re: Any Function Starts with a String and End with a String
                        AlbertoFaenza
                        948415 wrote:
                        This is the .txt file. Extracting the whole contecnt of the file and storing it in Clob Datatype v_data with UTL_HTTP package

                        c237z121206
                        h237z121206
                        a237z121206
                        c238z121207
                        h238z121207
                        a238z121207
                        c239z121210
                        h239z121210
                        a239z121210


                        Once i get all the data in v_data

                        I need to check for today sysdate in the YYMMDD format and the string starting from 'a'

                        So that i can retrieve the 'a239z121210'
                        Hi,

                        you can use this query to select your data:
                        SELECT MIN(str) 
                          FROM ( SELECT TO_CHAR(REGEXP_SUBSTR(v_data, '\w+'||TO_CHAR(SYSDATE,'YYMMDD'), 1, LEVEL)) str
                                   FROM mytable
                                CONNECT BY LEVEL <= REGEXP_COUNT(v_data, '\w+'||TO_CHAR(SYSDATE,'YYMMDD'))
                               ); 
                        i.e.:
                        WITH mytable (v_data) AS (
                        SELECT TO_CLOB(
                        'c237z121206
                        h237z121206
                        a237z121206
                        c238z121207
                        h238z121207
                        a238z121207
                        c239z121210
                        h239z121210
                        a239z121210') 
                        FROM DUAL
                        )
                        SELECT MIN(str) 
                          FROM ( SELECT TO_CHAR(REGEXP_SUBSTR(v_data, '\w+'||TO_CHAR(SYSDATE,'YYMMDD'), 1, LEVEL)) str
                                   FROM mytable
                                CONNECT BY LEVEL <= REGEXP_COUNT(v_data, '\w+'||TO_CHAR(SYSDATE,'YYMMDD'))
                               ); 
                        
                        MIN(STR)                                                                        
                        --------------------
                        a239z121210                                                                     
                        To use it in PL/SQL and store in a local variable simply use:
                        SELECT MIN(str) 
                          INTO yourvarhere
                          FROM ( SELECT TO_CHAR(REGEXP_SUBSTR(v_data, '\w+'||TO_CHAR(SYSDATE,'YYMMDD'), 1, LEVEL)) str
                                   FROM mytable
                                CONNECT BY LEVEL <= REGEXP_COUNT(v_data, '\w+'||TO_CHAR(SYSDATE,'YYMMDD'))
                               ); 
                        Please note: if your file is big and exceeding 32767 characters, maximum size for a VARCHAR2 variable in PL/SQL, then this solution is not working.
                        In this case you should consider handling input data in a different way, like loading into database as single records per each line (with SQL*Loader) or defining external table as already suggested.

                        Regards.
                        Al

                        Edited by: Alberto Faenza on Dec 10, 2012 2:36 PM
                        Note about size
                        • 9. Re: Any Function Starts with a String and End with a String
                          ascheffer
                          select regexp_substr( 
                          'c237z121206
                          h237z121206
                          a237z121206
                          c238z121207
                          h238z121207
                          a238z121207
                          c239z121210
                          h239z121210
                          a239z121210'
                          , '^a.*' || to_char( sysdate, 'yymmdd' ) || '$', 1, 1, 'm' )
                          from dual
                          • 10. Re: Any Function Starts with a String and End with a String
                            vijayrsehgal-Oracle
                            do you mean something like below:
                            declare
                            type ltable is table of varchar2(100);
                            lt ltable;
                            begin
                            with data as (
                            select 'c237z121206' col1 from dual
                            union all
                            select 'h237z121206' from dual
                            union all
                            select 'a237z121206' from dual
                            union all
                            select 'c238z121207' from dual
                            union all
                            select 'h238z121207' from dual
                            union all
                            select 'a238z121207' from dual
                            union all
                            select 'c239z121210' from dual
                            union all
                            select 'h239z121210' from dual
                            union all
                            select 'a239z121210' from dual
                            union all
                            select 'a239z121210121210' from dual
                            )
                            select col1 bulk collect into lt from data;
                            for i in 1..lt.count
                            loop
                            
                             if substr(lt(i),1,1) = 'a' and regexp_count(lt(i),'121210',1,'i') = 1 then 
                              dbms_output.put_line(lt(i));
                             end if;
                            end loop;
                            end;
                            Would still suggest you evaluate what you are trying to do, you can do it using sql and no need of pl/sql.
                            • 11. Re: Any Function Starts with a String and End with a String
                              951418
                              Can u tellme how u are able to check for the String whcih starts from 'a' as in the query mentioned by you i can see u are searching for 'a' String?
                              • 12. Re: Any Function Starts with a String and End with a String
                                AlbertoFaenza
                                948415 wrote:
                                Can u tellme how u are able to check for the String whcih starts from 'a' as in the query mentioned by you i can see u are searching for 'a' String?
                                I have assumed that you wanted to check for the MIN value, which is 'a' in this case. The code I've posted will retrieve the minimum string that is matching your date. However if you want to search always for a string starting with 'a' it's even simpler:
                                SELECT TO_CHAR(REGEXP_SUBSTR(v_data, 'a\w+'||TO_CHAR(SYSDATE,'YYMMDD'))) str
                                  FROM mytable;
                                i.e.
                                WITH mytable (v_data) AS (
                                SELECT TO_CLOB(
                                'c237z121206
                                h237z121206
                                a237z121206
                                c238z121207
                                h238z121207
                                a238z121207
                                c239z121210
                                h239z121210
                                a239z121210') 
                                FROM DUAL
                                )
                                SELECT TO_CHAR(REGEXP_SUBSTR(v_data, 'a\w+'||TO_CHAR(SYSDATE,'YYMMDD'))) str
                                  FROM mytable;
                                 
                                STR                                                                             
                                --------------------
                                a239z121210  
                                Remember the limitations about the size of your variable that I've mentioned before.

                                Regards.
                                Al

                                Edited by: Alberto Faenza on Dec 10, 2012 2:54 PM
                                • 13. Re: Any Function Starts with a String and End with a String
                                  ascheffer
                                  Or in plain plsql
                                  declare
                                   v_data clob := rpad( to_clob( 'c237z121206
                                  h237z121206
                                  a237z121206
                                  c238z121207
                                  h238z121207
                                  a238z121207
                                  c239z121210
                                  h239z121210
                                  a239z121210
                                  a239z121210
                                  b239z121210
                                  ' ), 50000, 'lots of junk' );
                                   v_check varchar2(32767);
                                  begin
                                    v_check := regexp_substr( v_data, 'a.*' || to_char( sysdate, 'yymmdd' ), 1, 1, 'm' );
                                    if v_check is not null
                                    then
                                      dbms_output.put_line( 'found ' || v_check );
                                    end if;
                                  end;
                                  No size limitations on the clob

                                  Edited by: ascheffer on Dec 10, 2012 3:12 PM
                                  No size limitations on the clob by the way
                                  • 14. Re: Any Function Starts with a String and End with a String
                                    AlbertoFaenza
                                    Hi Anthon,

                                    you are right. It was my fault. Actually REGEXP_SUBSTR does not have limitation on size and can work with CLOB. I thought it was doing an implicit conversion to VARCHAR2.

                                    So there is no limitation on size using REGEXP_SUBSTR.

                                    Regards.
                                    Al