Replace a variable part of a string in a varchar2 field
I have a vendor Oracle table containing data as follows
column name: PVC_Value
value = TRKCASETRK;6,0,0,0,0,0,0;;20801911;32004910;43175007;2,2;0;59647;1,30;0;0;3002
As you can see this field contains data delimited by semi-colons (for 13 elements)
I need to relace the 7th and 10th elements in this string, so in the case above I need to replace the substring 2,2 with 1,3 and the substring 1,30 with 2,3 for example
The overall length of this string can vary from record to record and there may or not be data for every element (I know it sucks, but I didn't design it), as seen in the third element (the ;;). There will always be data in the 7th and 10th elements though
My guess is that this may need a PL/SQL function to solve the problem and that's where my problems begin, with my limited PL/SQL experience.
Thanks in advance to those who accept the challenge.
Software/Hardware used:
Software/Hardware used:
ASKED:
October 23, 2008 9:06 PM
UPDATED:
October 24, 2008 7:19 PM
RELATED QUESTIONS
Answer Wiki:
You could play with the INSTR function, which returns the position of a specified substring within a string.
Usage:
instr(<string>,<substring>,<starting_position>,<occurrence>)
You can use this function to see the value of a specific element in the PVC_Value, for example, to see the 7th element, the following select should do the trick:
<pre>select substr(pvc_value,instr(pvc_value,';',1,6)+1,instr(pvc_value,';',1,7)-1-instr(pvc_value,';',1,6))
from your_table;</pre>
and to see the 10th elment:
<pre>select substr(pvc_value,instr(pvc_value,';',1,9)+1,instr(pvc_value,';',1,10)-1-instr(pvc_value,';',1,9))
from your_table;</pre>
Note that we are searching for the semicolon, so it should work even when some elements have no data.
You could do something similar to update the pvc_field, for example, to update the 7th element:
<pre>update your_table
set pvc_value = substr(pvc_value,1,instr(pvc_value,';',1,6))||'&new_value'||substr(pvc_value,instr(pvc_value,';',1,7),length(pvc_value));</pre>
If you are going to update the element with some fixed value, you can replace the '&new_value' with the fixed string of your choice. If you leave it as it is, when you run the statement, you will be prompted for the new value.
I'm not sure how you need to update the data, but I hope this at least gives you a light.
Regards,
To see all answers submitted to the Answer Wiki: View Answer History.
Thanks, this is exactly what I needed. I forgot the Instr can locate an instatance of a char.