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||'..'

, msib.INVENTORY_ITEM_ID
, organization_id
,ascii(substr(segment1,-1,1)) ascii

from mtl_system_items_b msib
where ascii(substr(segment1,-1,1))
NOT IN (
/*UPPERCASE*/

65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,
/*NUMBERS*/48,49,50,51,52,53,54,55,56,57,
/*LOWERCASE*/

97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,
/*SPECIAL*/95,40,41,39,46,37,44,96,63,42)
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