replace not working

Hi,
I am trying to run
RTRIM(COALESCE(replace(replace(ID,char(x'0B'),' '),char(x'01'),' '),'')) NAME,
db29.5/aix is not liking it any idea what can be alternative?

Thanks
sam2929Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi Sam,

What do you mean "not liking it"?  A parse error (probably on the hex values) or no replace is occurring?


Kent
sam2929Author Commented:
i am getting error below
SQL1822N  Unexpected error code "-418" received from data source
"aa_SERVER". Associated text and tokens are " SQL0418N  A statement
contains a use of a parame".  SQLSTATE=560BD
Kent OlsenDBACommented:
Hi Sam,

I run your query on one of my DB2 instances and it works fine.  I'm running 9.7.1.

That said, this is a DB2 bug.  I found a reference to it here:

  http://www-01.ibm.com/support/docview.wss?rs=3564&context=SSC2KZA&dc=DB560&dc=DB520&uid=swg21397273&loc=en_US&cs=utf-8&lang=en

There are a couple of work-arounds, depending on the exact cause, but the correct solution is to install the correct fixpak or upgrade DB2.


Kent
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

sam2929Author Commented:
ya looks like a fix pack but i guess this will take some time as i am not dba what alternative function i can use
Kent OlsenDBACommented:
Try the TRANSLATE function.  It actually provides for neater SQL, too.

select
  translate (ID, char(x'01') || char(x'0B'), '  ')
from {tablename};


Kent
sam2929Author Commented:
data format not good when i run translate
RTRIM(translate(ID,char(x'01')||char(x'0B'),' ')) AS NAME

aaemail#1
Kent OlsenDBACommented:
Oops.  Swap the parameters.  :)

  RTRIM(translate(ID,'  ', char(x'01')||char(x'0B'))) AS NAME


Kent

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam2929Author Commented:
Hi Kent,
i am trying to apply filter to see if i get desired result using
select RTRIM(translate(ID,'  ', char(x'01')||char(x'0B'))) AS NAME,
id
from table
where id like '%x%'

not getting results what i need is my filter right ? if i just want to compare id with translate results
Kent OlsenDBACommented:
Hi Sam,

The RTRIM (truncate ...) NAME function and the WHERE id LIKE '%x%' filters are completely independent.

The functions don't change the string in any way that would result in a character being changed to an 'x' or from an 'x', so filtering on '%x%' will yield the same result whether it's applied to 'x' or the value returned from the function.


Kent
sam2929Author Commented:
Kent,
When i use translate function query tooks forever to run any idea why?

Thanks
Kent OlsenDBACommented:
Hi Sam,

The TRANSLATE function shouldn't add that much time to the query unless it in the query filter (WHERE or HAVING clause).

Can you post the query?  Maybe a look at it will help.


Kent
sam2929Author Commented:
its good now might be some locks earlier .When i changed replace function with translate
and i looked at the data i see unwanted character between space
VARGRAPHIC(RTRIM(COALESCE(replace(local,char(x'01'),' '), ''))) obj,
changed to

VARGRAPHIC(RTRIM(translate(local,char(x'01'),' '))) obj,

data look like aaandFileNetMessagewillvaryper
Kent OlsenDBACommented:
Hi Sam,

It shouldn't be a lock issue.  It looks to me like the REPLACE function is attempting to honor the binary data that indicates a multi-byte character (probably thinking that it's UTF-8) while the TRANSLATE function is operating on the data as single-byte characters.


Kent
sam2929Author Commented:
Hi Kent,
When i do conversion from replace function to translate function is that the correct format?

VARGRAPHIC(RTRIM(COALESCE(replace(local,char(x'01'),' '), ''))) obj,
changed to

VARGRAPHIC(RTRIM(translate(local,char(x'01'),' '))) obj,

Kent OlsenDBACommented:

That looks right.  :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.