CODEWITHSUNDEEP
sqloracle-database
Craig
Craig

Reputation: 157

Replacing information between two Character breaks

I want to replace information which is between two character breaks "|"

The data in the column

Parameters_Sent_To_Procdure|Information_to_replace_|Put_the_existing_information_back

So I want to put the existing parameters back replace the information between the two Pipes "|" then put the information after the second pipe back

Upvotes: 0

Views: 890

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

I want to replace information which is between two character breaks "|"

The easiest way is to extract the substring and concatenate it with your desired value which you want to replace with and then concatenate everything back using:

  • SUBSTR
  • INSTR

SQL> WITH DATA AS
  2    (SELECT 'Parameters_Sent_To_Procdure|Information_to_replace_|Put_the_existing_information_back' str
  3    FROM dual
  4    )
  5  SELECT SUBSTR(str, 1, instr(str, '|', 1,1) -1)
  6    || ''
  7    || SUBSTR(str, instr(str, '|', 1, 1) + 1,
  8                   instr(str, '|', 1, 2) -
  9                  instr(str, '|', 1, 1) -1) AS "new_str"
 10  FROM data;

new_str
--------------------------------------------------------------------------------------------
Parameters_Sent_To_ProcdureInformation_to_replace_

How the query works:

SELECT SUBSTR(str, 1, instr(str, '|', 1,1) -1)

The above SUBSTR extracts the first part of the string, i.e. from the beginning of the string to the first occurrence of |. 

'<Here goes the string you want to replace>'

The above should be your new value.

SUBSTR(str, instr(str, '|', 1, 1) + 1, 
            instr(str, '|', 1, 2) - 
            instr(str, '|', 1, 1) -1)

The above SUBSTR extracts the last part of the string, i.e. from the second occurrence of | till the end of the string.

Upvotes: 2

Related Questions