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... (AT) hotmail (DOT) com wrote:
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 --

On May 7, 7:32*am, christopherc... (AT) hotmail (DOT) com wrote:
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 --

On May 7, 7:32*am, christopherc... (AT) hotmail (DOT) com wrote:
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 --

On May 7, 7:32*am, christopherc... (AT) hotmail (DOT) com wrote:
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 --