Replace a variable part of a string in a varchar2 field

15 pts.
Tags:
Oracle PL/SQL
Oracle Table
VARCHAR
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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,

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JoeA
    Thanks, this is exactly what I needed. I forgot the Instr can locate an instatance of a char.
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following