?
Solved

oracle function- modification required

Posted on 2011-10-03
7
Medium Priority
?
349 Views
Last Modified: 2012-05-12
I have a function
CREATE OR REPLACE FUNCTION strip_bad (p_string IN VARCHAR2)
      RETURN VARCHAR2
    IS
       good_string VARCHAR2(62)
          := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ||
             'abcdefghijklmnopqrstuvwxyz' ||
             '1234567890';
    BEGIN
      RETURN
       TRANSLATE (p_string, 'T' ||
                              TRANSLATE ( p_string, '.' || good_string
                                                 ,  '.'
                                       ) -- find the bad characters
                         ,  'T');  -- remove the bad characters
  END;
/
This functions removes bad characters.

If the user enters
added more blood bags
its takes off the space and inserts as
addedmorebloodbags.
How can I modify this fucntion to make sure that spaces between words are not removed? Help appreciated.
0
Comment
Question by:anumoses
  • 4
  • 3
7 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 36905380
Add a space to the 'good' characters:

...
:= ' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
...
0
 
LVL 6

Author Comment

by:anumoses
ID: 36905388
CREATE OR REPLACE FUNCTION strip_bad (p_string IN VARCHAR2)
      RETURN VARCHAR2
    IS
       good_string VARCHAR2(62)
          := ' ABCDEFGHIJKLMNOPQRSTUVWXYZ' ||
              ' abcdefghijklmnopqrstuvwxyz' ||
              ' 1234567890';
    BEGIN
      RETURN
       TRANSLATE (p_string, 'T' ||
                              TRANSLATE ( p_string, '.' || good_string
                                                 ,  '.'
                                       ) -- find the bad characters
                         ,  'T');  -- remove the bad characters
  END;
/
Like this?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36905415
Looks good to me if that is the only thing you changed (adding a space before the 'ABC...'.

On your test database, try it.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Author Comment

by:anumoses
ID: 36905418
I made the change and ran this

select strip_bad('I am ready to compile') from dual;

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "HBC_DATA.STRIP_BAD", line 4
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 36905459
I missed the definite width on the varchar2.

Change:
good_string VARCHAR2(62)

to:
good_string VARCHAR2(63)
0
 
LVL 6

Author Comment

by:anumoses
ID: 36905470
that worked. thanks.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 36905508
thanks again
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question