Pages

Monday, December 13, 2010

DB2 Remove Special Character (UDF)

Db2 Database comes w/ the vast collection of encoding that some case our application(front-end) is unable to render. According to unicode.org , Java default encoding is UTF-16. The user define function(UDF) below can be used to filter the special character upon retrieving the column value.

CREATE FUNCTION "Schema"."REMOVESPECIALCHAR" ( "PARAM_STR" VARCHAR(1024) )
  RETURNS VARCHAR(1024)
  SPECIFIC "REMOVESPECIALCHAR"
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  READS SQL DATA
  CALLED ON NULL INPUT
  INHERIT SPECIAL REGISTERS
begin atomic
    declare results varchar(1024) default '';
    declare validchar varchar(256) default 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*()-=+/\{}[];:.,<>?_| abcdefghijklmnopqrstuvwxyz~`';
    declare ch varchar(16);
    declare counter smallint default 0;
   
    while counter < CHARACTER_LENGTH(param_str,CODEUNITS16) do
        set counter =  counter  +  1;
                set ch = SUBSTRING(param_str,counter,1,CODEUNITS16);
                set results = results concat replace(ch,TRIM(TRANSLATE(ch,'',validchar)),' ');
  end while;
    return     case trim(results) when '' then null else trim(results) end;
end;


- you can also enhance the declaration of validchar to fetch list of valid character on your source table.

1 comment: