asked on

Regular expression

I have attached a code snipptte of a Stored Procedure from the code snipptte
The code V_CNT_QRY := 'select LENGTH(TRIM(TRANSLATE(:1, ''abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'', '' ''))) from dual'; checks for any special characters however is there a better way to achieve the above without using translate ?

BEGIN
            V_LEN_QRY := 'SELECT LENGTH(:1) FROM DUAL';
            EXECUTE IMMEDIATE V_LEN_QRY INTO V_LEN USING V_COLUMN_DATA;

            IF(V_LEN > V_MAX_LENGTH) THEN
                V_ERROR_CD := 'E06';
            ELSE
                V_CNT_QRY := 'select LENGTH(TRIM(TRANSLATE(:1, ''abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'', '' ''))) from dual';
                EXECUTE IMMEDIATE  V_CNT_QRY INTO V_CNT USING V_COLUMN_DATA;

                IF(V_CNT IS NOT NULL) THEN
                    V_ERROR_CD := 'E';
                END IF;

            END IF;

Open in new window

Sean Stuber

yes and you don't want to use execute immediate or a select statement either

v_cnt := length(regexp_replace(v_column_data,'[^a-zA-Z0-9]'));

Sean Stuber

similarly

V_LEN := length(V_COLUMN_DATA);

don't use select statements to do simple assignments and definitely don't use execute immediate for static sql

Sean Stuber

oops, I misread your intent, you want to find the non-alphanumeric, not remove them

same idea, but remove the negation

v_cnt := length(regexp_replace(v_column_data,'[a-zA-Z0-9]'));

Sean Stuber

I also note your translate will replace "a" with " "

is that really the intent?

ronan_40060

ASKER

Thanks sdstuber . can you please eleborate -->don't use select statements to do simple assignments and definitely don't use execute immediate for static sql
I would like to know the reason
Thanks
Ronan

BEGIN  
            V_LEN := length(V_COLUMN_DATA);
            IF(V_LEN > V_MAX_LENGTH) THEN  
                V_ERROR_CD := 'E06';  
            ELSE  
               v_cnt := length(regexp_replace(v_column_data,'[^a-zA-Z0-9]')); 
             IF(V_CNT IS NOT NULL) THEN  
                    V_ERROR_CD := 'E';  
                END IF;  
  
END IF;

Open in new window

ronan_40060

ASKER

yes translate will replace "a" with " " so my final code with your inputs looks like

BEGIN    
            V_LEN := length(V_COLUMN_DATA);  
            IF(V_LEN > V_MAX_LENGTH) THEN    
                V_ERROR_CD := 'E06';    
            ELSE    
               v_cnt := length(regexp_replace(v_column_data,'[a-zA-Z0-9]'));   
             IF(V_CNT IS NOT NULL) THEN    
                    V_ERROR_CD := 'E';    
                END IF;    
    
END IF;

Open in new window

Sean Stuber

besides the obvious extra code and complexity added

sql and pl/sql run in different "engines" , you suffer a context switch when you toggle between them.
It's a minor lag, but a penalty nonetheless and completely avoidable.

similarly execute immediate is extra over head on top of the context switch as well. It also opens the opportunity for sql inject, but in your usage you were safe from that particular problem.

execute immediate is for running statements that change or for things that can't be run directly within pl/sql, like ddl statements. Using it to run a static sql is again simply adding extra processing for absolutely no benefit.

Sean Stuber

running that code one time will produce no human detectable performance difference but if your code runs multiple times and is a standard throught your application then you're incurring extra performance cost, consuming more cpu and memory with 0 benefit. If code like that runs in loops the problems will be even worse.

Sean Stuber

>>>> yes translate will replace "a" with " "

I know, but is that intentional? my regular expression won't replicate that functionality

ronan_40060

ASKER

>>>>>my regular expression won't replicate that functionality
you mean v_cnt := length(regexp_replace(v_column_data,'[a-zA-Z0-9]')); ??
what I came to know is that REGEXP_REPLACE function is similar to the TRANSLATE function except that it replaces a string pattern, specified with a regular expression, instead of a string literal.
\ v_cnt := length(regexp_replace(v_column_data,'[a-zA-Z0-9]', ' ')); should also work

Sean Stuber

your translate removes all alphanumeric characters except "a" which is replaces with " "
my regular expression removes all alphanumeric characters.

my question is, what do you "want" to have happen?

ronan_40060

ASKER

Thanks a lot for your details explanations :)
The intent is to replacer any special character with '' , so the translate in my PL?SQL code would remove all alphanumeric characters except "a" which is incorrect .
Thanks for pointing it out.
My intent is to remove any special characters and replace with ' '.
so final syntax would be
v_cnt := length(regexp_replace(v_column_data,'[a-zA-Z0-9]', ' '));

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

also note ' ' is a space
maybe you meant '' (two single quotes with nothing between them)

if that is what you meant, just use the keyword NULL, it removes any visual-ambiguity

ronan_40060

ASKER

Thanks again
I had a look at the whole procedure and TRANSLATE would check of a any special character in my previouos code and if any special character is found , an error code is thrown.
So regexp_replace(v_column_data,'[a-zA-Z0-9]') is exactly what Im looking for as it would check 'v_column_data' for any special characters and if found , an error would be thrown .
regexp_replace(v_column_data,'[a-zA-Z0-9]') should serve my purpose.
Thank you for your inputs and time,

ronan_40060

ASKER

Excellent