Conversion

[nikkiheave]

I have a list of values that I need to convert, and was wondering if their was a substring or replacement funtion, I could use:

For example:

Instead of it being P7 "0" the number, it need to be P7"O" the letter, so it should read

P70:003 should be P7O:00

helP!!!!!!!!!!!!!!!

[dknight]

If there are regular patterns, you may be able to use the INSTR or "in string" function.

You can find the nth occurrance of a string 'ABC' beginning at the pth position of field Field1 with

INSTR(Field1, 'ABC', p, n)

You may need to use SUBSTR and concatenate the results. It depends on your situation.

Good luck.

[nealh]

If it's always P7O, you can use replace:

select replace('P7O:003', 'P70', 'P7O')
from dual

If it's just the third character you want to change, you could do:

select translate(substr('P7O:003',1,3), '0', 'O')||
substr('P7O:003',4)
from dual

[reydp]

this is just an example of what Dave mentioned
about pattern:
Here the pattern is there's always ':' between
alphanumeric(P7O) and all numeric (003).

Assuming the value of var1 is 'P70:003':

select replace(substr(var1,1,instr(var1,':',1,1)),'0','O') || substr(var1,instr(var1,':',1,1)+1) "Conversion"
from dual;

Conversion
----------
P7O:003
SQL>