• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3676
  • Last Modified:

DB2 stored procedure for removing the special character from db

hi all,
 i want to have one stored procedure to remove special characters from dbase and print the report
 and after that it will concatenate the other string and update the db.
i am using db2.
ex-- ASDBH&WE -----> ASDBHWE
 
        ASDB_EW ----->ASDBEW

       ASEDE%DV---->ASEDEDV
       
0
mukeshkt
Asked:
mukeshkt
  • 5
  • 3
  • 3
  • +1
3 Solutions
 
momi_sabagCommented:
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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi mukeshkt,

Momi's on the right track and if the value will have no more than 1 type of special character (only '&' or only '%', etc.) use his solution.  Order the CASE conditions according to likelihood.

If the value could contain several different special characters, you'll probably want to catch all of them with one statement.  Nesting replace() functions will work fine.

UPDATE mytable
SET somecolumn =
  replace (replace, (replace (somecolumn, '&', ''), '_', ''), '%', '')

Variations on this will report on the columns to be affected:

SELECT id, somecolumn, replace (replace, (replace (somecolumn, '&', ''), '_', ''), '%', '')
FROM mytable
WHERE somecolumn <> replace (replace, (replace (somecolumn, '&', ''), '_', ''), '%', '')


Good Luck,
Kent
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

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
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
Kent OlsenData Warehouse Architect / DBACommented:

Hi, Dave!

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


Kent


0
 
Dave FordSoftware Developer / Database AdministratorCommented:

Simple enough ...

update ...
set text = translate(text,'','&_%', '');
0
 
momi_sabagCommented:
translate can not remove a character, it can only replace characters
you could use your translate to change the bad characters to blanks and then use replace to remove them
but this will require more work so i used the replcae directly
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

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
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

or more accurately:

replace(translate(avarchar25,'~','&_%', '~'),'~', '')
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

> Speaking of questionable characters

Hey, I resemble that remark!  :-)  :-)
0
 
mukeshktAuthor Commented:
please close this question
0
 
momi_sabagCommented:
was your problem solved?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now