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

LVL 9
ronan_40060Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
>>>My intent is to remove any special characters and replace with ' '.

you can't both remove and replace,  it's one or the other.

if your intent is to simply replace characters with a different character then the length won't change, don't do a replace of any kind   v_cnt := length(v_column_data)

if you want to REMOVE characters then use the regexp_replace like I did without the 3rd parameter,  or use NULL

if you want to REMOVE SPECIAL characters and KEEP ALPHANUMERIC then expression should be regexp_replace(v_column_data,'[^a-zA-Z0-9]')
if you want to REMOVE ALPHANUMERIC and KEEP SPECIAL characters then expression should be  regexp_replace(v_column_data,'[a-zA-Z0-9]')
0
 
sdstuberCommented:
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]'));
0
 
sdstuberCommented:
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
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
sdstuberCommented:
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]'));
0
 
sdstuberCommented:
I also note your translate will replace "a" with " "

is that really the intent?
0
 
ronan_40060Author Commented:
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

0
 
ronan_40060Author Commented:
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

0
 
sdstuberCommented:
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.


0
 
sdstuberCommented:
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.
0
 
sdstuberCommented:
>>>> yes translate will replace "a" with " "

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

>>>>>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  
0
 
sdstuberCommented:
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?
0
 
ronan_40060Author Commented:
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]', ' '));
0
 
sdstuberCommented:
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
0
 
ronan_40060Author Commented:
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,
 
0
 
ronan_40060Author Commented:
Excellent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.