• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3826
  • 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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