SQL REPLACE function

From: <christophercash_at_hotmail.com>
Date: Wed, 7 May 2008 04:32:50 -0700 (PDT)
Message-ID: <f73f5d16-c37b-4100-9997-2f8545121ca6@k13g2000hse.googlegroups.com>

Hi Guys,

My database has records with foreign characters at the end of the string that need to be updated to a '-'.

I have used the following script to identify these records is:

select msib.segment1||'..'

, organization_id
,ascii(substr(segment1,-1,1)) ascii

from mtl_system_items_b msib
where ascii(substr(segment1,-1,1))


ORDER BY MSIB.SEGMENT1 I want to use a script like below which works for the records which have a ' ' at the end of the field which works fine.

update mtl_system_items_b
set segment1=REPLACE(segment1,' ','-')
where SUBSTR(segment1,-1,1) = ' '

Does anyone know how or if it is possible to use a script like this which can be used for all of the foreign characters? I guess a I need a WHERE clause somewhere after the REPLACE where I can specify all the ascii codes I want to update to '-'?

Any help would be great

Thanks Received on Wed May 07 2008 - 06:32:50 CDT

Original text of this message