DB2 stored procedure for removing the special character from db

what do you mean - remove from db?
do you need to remove from a specific table or from all tables ?
basically this can be done using a single sql statement

update table_name
set column_name = case when posstr(column_name, '&') > 0 then replace(columns_name, '&', '')
   when posstr(column_name, '_') > 0 then replace(columns_name, '_', '')
   when posstr(column_name, '%') > 0 then replace(columns_name, '%', '')
 end

Here's the way I usually accomplish that kind of thing:

upda
set text = translate(text,' ','&_%', ' ');

I find that much simpler than multiple levels of REPLACE functions.

HTH,
DaveSlash

Hi, Dave!

Great idea, except that I think the poster wants the character(s) removed from the name, not replaced with a blank.


Kent

Excellent point, Momi.

How about this:
replace(translate(avarchar25,'~','abc', '~'),'~', '')

As long as you can ensure that there are no tildes in the string, then that should work.

-- DaveSlash

or more accurately:

replace(translate(avarchar25,'~','&_%', '~'),'~', '')

Hi Dave.

Just change all of the questionable characters to the same questionable character with translate, then delete it with a call to replace.  No hunting for an unused character is necessary.

  replace(translate(avarchar25,'&','_%', '&'),'&', '')


Speaking of questionable characters...

Well, never mind.  :)


Kent

> Speaking of questionable characters

Hey, I resemble that remark!  :-)  :-)

please close this question

was your problem solved?