Re: SQL REPLACE function

On May 7, 7:32�am, christopherc..._at_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,110,111,112,113,114,115,11�6,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

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 -- Received on Wed May 07 2008 - 11:35:29 CDT