SQL REPLACE function

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,1 10,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

On May 7, 7:32*am, christopherc...@hotmail.com wrote:
> 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,1 10,111,112,113,114,115,11*6,117,118,119,120,121,12 2,
> /*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

What about the translate function which will convert each character in
the first list to the corresponding character in the second list?

See the SQL manual for full details.
UT1 > l
1 select fld1, translate(fld1,'abcde','ABCDE')
2* from marktest
UT1 > /

FLD1 TRANSLATE(
---------- ----------
one onE
TWO TWO

Warning translate is all occurrences. For position specific changes
if on 10g see the regular expression functions.

HTH -- Mark D Powell --