Replacing only 3rd to 6th position of a String-CodePudding

I am new to SQL. I want to replace only 3rd to 6th(only 1st 1234) position of below string with '1219', but its replacing whole new string:

SELECT REPLACE('DD123412341234',SUBSTR('DD123412341234',3,4),'1219' ) FROM DUAL;

Kindle suggest on the same.

CodePudding user response：

select regexp_replace('DD123412341234',SUBSTR('DD123412341234',3,4),'1219',3,1) FROM DUAL;

DD123412341234: original string

SUBSTR('DD123412341234',3,4): a string that you want to replace

1219: string to replace with

3: starting position

1: occurrence

CodePudding user response：

You can do it with function regexp_replace.

select regexp_replace('DD123412341234','....','1219',3,1) as RESULT from DUAL

As stated in the documentation, the second function parameter is the regular expression to search for which, in this case, is any four characters. The third parameter is the replacement string. The fourth [optional] parameter indicates to start the search from the third character and the final [also optional] parameter means only search for the first occurrence and replace that first occurrence with 1219.

The result of the above query is DD121912341234