I have given 'THOMAS,RANJAN,JDJDJDJ,OOOO'
I want to mask 1st 3rd 4th character with x in Postgres
If you want the replacement to occur for all four comma separated parts, you can use something like this:
WITH mytable(mystring) AS (SELECT 'THOMAS,RANJAN,JDJDJDJ,OOOO'::text)
SELECT string_agg(
overlay(
overlay(
a.p
placing 'x' from 1 for 1
)
placing 'xx' from 3 for 2
),
','
)
FROM mytable
CROSS JOIN LATERAL
unnest(string_to_array(mystring, ',')) a(p);
That results in
┌────────────────────────────┐
│ string_agg │
├────────────────────────────┤
│ xHxxAS,xAxxAN,xDxxJDJ,xOxx │
└────────────────────────────┘
(1 row)
If you don't need the replacement to occur for all parts, but for the whole string, just use the two overlays and forget the rest.
You can also use regex. One regex to obtain the places of insertion (where x should be) and another one to obtain the rest of substrings (where the text should remain intact).
The following should work also for shorter strings.
SELECT test.replace_string_134('THOMAS,RANJAN,JDJDJDJ,OOOO,b,bl,bla');
yield: xHxxAS,xAxxAN,xDxxJDJ,xOxx,x,xl,xlx
I post this as an alternate sample but for performance wise you can follow @LaurenzAlbe answer.
CREATE SCHEMA test;
CREATE OR REPLACE FUNCTION test.replace_string_134 (TEXT)
RETURNS TEXT
AS
$$
DECLARE
input_text ALIAS FOR $1;
BEGIN
RETURN (
SELECT STRING_AGG(
CASE WHEN tgroup.txt_group[1] <> '' THEN 'x' ELSE '' END
|| trest.txt_rest[1]
|| CASE WHEN tgroup.txt_group[2] <> '' THEN 'x' ELSE '' END
|| CASE WHEN tgroup.txt_group[3] <> '' THEN 'x' ELSE '' END
|| trest.txt_rest[2],
',')
FROM
(
SELECT txt_group, ROW_NUMBER() OVER () AS txt_index
FROM regexp_matches(input_text, '[,]?([^,])[^,]?([^,]?)([^,]?)[^,]*[,]?', 'g') AS txt_group
) AS tgroup
INNER JOIN
(
SELECT txt_rest, ROW_NUMBER() OVER () AS txt_index
FROM regexp_matches(input_text, '[,]?[^,]([^,]?)[^,]?[^,]?([^,]*)[,]?', 'g') AS txt_rest
) AS trest
ON trest.txt_index = tgroup.txt_index
);
END
$$
LANGUAGE plpgsql;
SELECT test.replace_string_134('THOMAS,RANJAN,JDJDJDJ,OOOO,b,bl,bla');
DROP SCHEMA test CASCADE;