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
|
