I have a varchar2 column with value as - '20210103 20110109' which I want to change 2011 as 2022 that looks like '20210103 20220109'

CodePudding user response：

The way you put it, it is just a simple replace:

SQL> select replace('20210103 20110109', '2011', '2022') result from dual;

RESULT
-----------------
20210103 20220109

SQL>

Could be more complicated if there were other 2011 substrings involved (but that's not what you said).

CodePudding user response：

If you want to display different data then, if you only want to change the single value then you can use:

CASE column_name
WHEN '20210103 20110109'
THEN '20210103 20220109'
ELSE column_name
END

If you want to display all 2011 values in that substring as 2022 then you can use:

CASE
WHEN SUBSTR(column_name, 10, 4) = '2011'
THEN SUBSTR(column_name, 1, 9) || '2022' || SUBSTR(column_name, 14)
ELSE column_name
END

For example, given the sample data:

CREATE TABLE table_name (column_name) AS
SELECT '20210103 20110109' FROM DUAL UNION ALL
SELECT '20210103 20120109' FROM DUAL UNION ALL
SELECT '20210103 20130109' FROM DUAL UNION ALL
SELECT '20210103 20140109' FROM DUAL UNION ALL
SELECT '20212011 20112011' FROM DUAL;

Then:

SELECT column_name,
       CASE column_name
       WHEN '20210103 20110109'
       THEN '20210103 20220109'
       ELSE column_name
       END AS replacement1,
       CASE
       WHEN SUBSTR(column_name, 10, 4) = '2011'
       THEN SUBSTR(column_name, 1, 9) || '2022' || SUBSTR(column_name, 14)
       ELSE column_name
       END AS replacement2
FROM   table_name

Outputs:

COLUMN_NAME	REPLACEMENT1	REPLACEMENT2
20210103 20110109	20210103 20220109	20210103 20220109
20210103 20120109	20210103 20120109	20210103 20120109
20210103 20130109	20210103 20130109	20210103 20130109
20210103 20140109	20210103 20140109	20210103 20140109
20212011 20112011	20212011 20112011	20212011 20222011

If you just want to UPDATE a single value:

UPDATE table_name
SET    column_name = '20210103 20220109'
WHERE  column_name = '20210103 20110109'

or, if you want to change all substrings between the 10^th and 13^th characters from 2011 to 2022:

UPDATE table_name
SET    column_name = SUBSTR(column_name, 1, 9) || '2022' || SUBSTR(column_name, 14)
WHERE  SUBSTR(column_name, 10, 4) = '2011'

db<>fiddle here