Avatar of anumoses
anumosesFlag for United States of America

asked on 

oracle function- modification required

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.
Oracle Database

Avatar of undefined
Last Comment
anumoses
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of anumoses
anumoses
Flag of United States of America image

ASKER

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?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Looks good to me if that is the only thing you changed (adding a space before the 'ABC...'.

On your test database, try it.
Avatar of anumoses
anumoses
Flag of United States of America image

ASKER

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
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of anumoses
anumoses
Flag of United States of America image

ASKER

that worked. thanks.
Avatar of anumoses
anumoses
Flag of United States of America image

ASKER

thanks again
Oracle Database
Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo