This discussion is archived
14 Replies Latest reply: Dec 10, 2012 6:25 AM by AlbertoFaenza RSS

Any Function Starts with a String and End with a String

951418 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    veejays_user10302525-Oracle Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    veejays_user10302525-Oracle Journeyer
    Currently Being Moderated
    Please clarify what are intend to do using pl/sql?
  • 5. Re: Any Function Starts with a String and End with a String
    951418 Newbie
    Currently Being Moderated
    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
    veejays_user10302525-Oracle Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    veejays_user10302525-Oracle Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points