advanced code snippet search

We Recommend

SQL Cookbook SQL Cookbook
Written in O'Reilly's popular Problem/Solution/Discussion style, the SQL Cookbook is sure to please. Anthony's credo is: "When it comes down to it, we all go to work, we all have bills to pay, and we all want to go home at a reasonable time and enjoy what's still available of our days." The SQL Cookbook moves quickly from problem to solution, saving you time each step of the way.

Posted By

spacebar on 10/14/07


Tagged

character strip function remove Oracle special


Versions (?)


Strip(i.e. remove) special character(s)

Published in: SQL 

Plain TextExpand 
  1. /* Strip(remove) special character(s) from a string.
  2.    Parameters -1-: String to process
  3.               -2-: Special character to strip
  4.    Note: If second parameter is null then the following list is used:
  5.          <tab>, <single quote>, <double quote>, <\>, <|>, <`>, <~>, <^>
  6. */
  7. FUNCTION strip_spc_character ( in_string IN varchar2,
  8.                                in_chars  IN varchar2 DEFAULT NULL )
  9.   RETURN varchar2
  10. IS
  11.   tab	              char(      1 ) := chr(9);
  12.   double_quote        char(      1 ) := chr(34);
  13.   single_quote        char(      1 ) := chr(39);
  14.   mask                varchar2( 80 ) := '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  15.   v_special_chars     varchar2( 80 ) := '`~^\|'||double_quote||single_quote||tab;
  16.   begin
  17.     IF in_chars IS NOT NULL then
  18.        v_special_chars := in_chars;
  19.     end IF;
  20.   RETURN translate( in_string, mask || v_special_chars, mask );
  21.   exception
  22.     when others then
  23.       RETURN in_string;
  24.   end strip_spc_character;

Report this snippet 

You need to login to post a comment.