Replace character at a given position in oracle

Question

Let us say I have a string of 16k characters which may contain a comma(,) at many positions but I want to replace the commas(,) at the position 4001,8001,12001 only with null

There may be commas(,) at other positions for example 4002 which shouldn't be replaced.

Sample data below

        WITH data
         AS (SELECT To_clob(Lpad('A', 4000, '0'))
                    ||To_clob(Lpad('A', 4000, '0'))
                    ||To_clob(Lpad('A', 4000, '0'))
                    ||To_clob(Lpad('A', 4000, '0'))
                    ||To_clob(Lpad('A', 4000, '0')) AS file_data
             FROM   dual),
         data1
         AS (SELECT LEVEL
                    lvl
                    ,
                    Substr(file_data, Decode(LEVEL, 1, LEVEL,
                                                    ( ( LEVEL - 1 ) * 4000 ) + 1),
                    4000)
                       file_data,
                    1
                       order1
             FROM   data
             CONNECT BY ( ( LEVEL - 1 ) * 4000 ) + 4000 <= Length(file_data))
    SELECT Wm_concat(To_clob(file_data)) file_data
    FROM   data1;

Replace with what? And where - in a table? Only in the output of a query? Then - is it guaranteed that the input is 16k characters, and there are commas at positions 4001, 8001, 12001 - or do those have to be checked first? — user5683823
– user5683823, Commented Apr 7, 2020 at 6:15
replace with null only in the output of the query ,there is no gaurantee that the input is 16k and there are commas at every multiples of 4000+1 positions — psaraj12
– psaraj12, Commented Apr 7, 2020 at 6:17
wm_concat was never documented (and therefore supported), and on my version, 12.2, it is not recognized. However, if it does work for you, here is a possible approach: chunk the input into 4000 character pieces; then, for each piece, compute nullif(substr(piece, 1, 1), ',') || substr(piece, 2); and then call wm_concat on these results. — user5683823
– user5683823, Commented Apr 7, 2020 at 6:31
on a generic query how to remove character say comma from a specific location where there an other commas — psaraj12
– psaraj12, Commented Apr 7, 2020 at 6:36

Arun Palanisamy · Accepted Answer · 2020-04-07 06:57:36Z

1

You can use REGEX_REPLACE to replace characters at certain position.

SELECT 
    REGEXP_REPLACE('This,is,mystring,with,16kchars,imtrying,toreplace,comma,at30th,position','(^.{30})(.{1})(.*)$','\1\3') 
FROM dual;

Output.

 This,is,mystring,with,16kcharsimtrying,toreplace,comma,at30th,position

You can change your position accordingly. But this is not specific to comma. It replaces anything with at the given position

edited Apr 7, 2020 at 6:57

answered Apr 7, 2020 at 6:54

Arun Palanisamy

5,4898 gold badges34 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

psaraj12 Over a year ago

What if I want to replace ; with null and also ; with comma

Arun Palanisamy Over a year ago

If you want to replace comma with ; instead of null, change the last part to '\1;\3'

Collectives™ on Stack Overflow

Replace character at a given position in oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related