Two Variables in Postgresql:
V_VAR :='US185268001,US285268002,US385268005'; --Input
V_OUT := '001,002,005'; --Expected output to be stored
Any idea how to do in postgresql? Last 3 characters of each comma separated value. How to manipulate the V_VAR variable to get the output as V_OUT variable.
Update:
Please Note: V_VAR can have any number of CSV list. Here i have 3 values for the example purpose.
Thanks, Karthik
We can try using a regex replacement here:
SELECT
col,
REGEXP_REPLACE(col, '.*?([0-9]{3})(,|$)', '\1\2', 'g') AS col_out
FROM yourTable;
The strategy here is to match lazily until matching (and capturing) the final 3 digits of each code in one group, and an optional comma in a second group. Then, we replace with just the three digits and optional comma separator.
Note: As @BillKarvin has pointed out, storing unnormalized CSV is not optimal database design. Please consider getting each code onto a separate record if possible.