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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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:
>>>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.