4 Replies Latest reply on Jun 6, 2014 6:26 PM by Kodiak_Seattle

    help me to breakout strings to interpolate symbols in REGEXP_LIKE

    Kodiak_Seattle

      So I have this PL/SQL that works great for me and I needed to add another condition, which is a REGEXP_LIKE.. and I heard that

      PL/SQL has a problems with special symbols and I need to break them out by interpolating, here is the line that I added and I am not sure how to do that...

       

      or REGEXP_LIKE (ADDR_DELVRY, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}')

       

      Thank you!

        • 1. Re: help me to breakout strings to interpolate symbols in REGEXP_LIKE
          Frank Kulash

          Hi,

           

           

          Kodiak_Seattle wrote:

           

          So I have this PL/SQL that workes great for me and I needed to add another condition, which is a REGEXP_LIKE.. and I heard that

          PL/SQL has a problems with special symbols and I need to break them out by interpolating, here is the line that I added and I am not sure how to do that...

           

          or REGEXP_LIKE (ADDR_DELVRY, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}')

           

          Thank you!

          Sorry, Ic an't ell what you want to do, or what the problem is.

           

          Are you trying to test if a given string meets a certain condition?  If so, explain what the conditon is.  Create a table with 5 or 10 test strings.  Post the CREATE TABLE and INSERT statments, and the results you want to get (e.g. 'Good' or 'Bad' for each string).

          See the forum FAQ: https://forums.oracle.com/message/9362002#9362002

          • 2. Re: help me to breakout strings to interpolate symbols in REGEXP_LIKE
            Kodiak_Seattle

            so this string:

             

            or REGEXP_LIKE (ADDR_DELVRY, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}')

             

            in SQL is fine, but when I put it into a PL/SQL it gives off errors and I was told that this is due to special characters that need to be broken out...

             

            • Error(24,33): PLS-00103: Encountered the symbol "[" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem   <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset

             

             

            Here is the whole thing:

             

            SQL_STMT_C_1 varchar2(2000) := 'create table TABLE_TEST COMPRESS as

            select /*+ parallel(AUTO) */ a.cust_skey, a.name_first, a.name_last, a.ADDR_DELVRY, a.ADDR_DELVRY2,

            a.ADDR_CITY_NAME, a.ADDR_STATE_NAME, a.ADDR_POSTAL_CODE,  b.MKT_MAIL_PREF_IND

            from Table_A a, Table_B b

            where a.cust_skey = b.cust_skey

            and (

            a.A_SURV_DT >= 20101028 and b.MAIL_IND IN (''Y'',''U'')

            and ( instr(upper(a.ADDR_DELVRY), ''XX'') > 0 OR instr(upper(a.ADDR_DELVRY2), ''XX'') > 0 OR instr(upper(a.addr_city_name), ''XX'') > 0 )

            or REGEXP_LIKE (ADDR_DELVRY, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}')

            )';

            • 3. Re: help me to breakout strings to interpolate symbols in REGEXP_LIKE
              Frank Kulash

              Hi

               

               

              Kodiak_Seattle wrote:

               

              so this string:

               

              or REGEXP_LIKE (ADDR_DELVRY, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}')

               

              in SQL is fine, but when I put it into a PL/SQL it gives off errors and I was told that this is due to special characters that need to be broken out...

               

               

              Here is the whole thing:

               

              SQL_STMT_C_1 varchar2(2000) := 'create table Y775.TABLE_TEST COMPRESS as

              select /*+ parallel(AUTO) */ a.cust_skey, a.name_first, a.name_last, a.ADDR_DELVRY, a.ADDR_DELVRY2,

              a.ADDR_CITY_NAME, a.ADDR_STATE_NAME, a.ADDR_POSTAL_CODE,  b.MKT_MAIL_PREF_IND

              from Table_A a, Table_B b

              where a.cust_skey = b.cust_skey

              and ( a.A_SURV_DT >= 20101028 and b.MAIL_IND IN (''Y'',''U'')

              and ( instr(upper(a.ADDR_DELVRY), ''XX'') > 0 OR instr(upper(a.ADDR_DELVRY2), ''XX'') > 0 OR instr(upper(a.addr_city_name), ''XX'') > 0 )'

              or REGEXP_LIKE (ADDR_DELVRY, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}') )';

              Is that really the whole thing?  Are you sure there's not (for example) a DECLARE statement before that?

               

              Is the last line you posted above supposed to be part of the string literal being assigned to sal_stmt_c_1?  If so, don't en the literal at the end of the line before that.

              Remember, if you want to have a single-quote inside a string literal, then you have to use 2 single-quotes in a row.  You did this correctly before the last line, for example, when you said:

              ... and ( a.A_SURV_DT >= 20101028 and b.MAIL_IND IN (''Y'',''U'') ...

              Do the same kind of thing in the last line (or use Q-notation for the string literal). See the SQL Language manual

              http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#sthref347

              • 4. Re: help me to breakout strings to interpolate symbols in REGEXP_LIKE
                Kodiak_Seattle

                Cool, it was easy :- )

                 

                I removed the tick-mark from the next-to last tine at the end:    a.addr_city_name), ''XX'') > 0 ) '

                 

                and then added another tick-mark in the REGEXP_LIKE and that fixed it, thanks Frank!

                 

                Yes there is more code, but the rest of it is fine, this is where the change happened, thanks!!!!

                 

                and   (instr(upper(a.ADDR_DELVRY),       ''XX'') > 0 OR instr(upper(a.ADDR_DELVRY2),      ''XX'') > 0 OR instr(upper(a.addr_city_name),    ''XX'') > 0)

                or    REGEXP_LIKE (a.ADDR_DELVRY, ''[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}'') )';