I am quite new in regular expression so I am having confusion in replacing numbers inside a string, in which several id numbers (from 1 to 5 digits, up to now) are separated by commas and closed between curly brackets. I need to put before each written number a fixed code like p1_ in order to distinguish in future different types of objects' ids. I have postgres database with a column "maintainance" in text format which can contains values like the followings (cells CAN'T BE null or empty):
+---------------+
| maintainance |
+---------------+
| {12541,2,4} |
+---------------+
| {12,131,9999} |
+---------------+
| {54} |
+---------------+
| {1} |
+---------------+
| {12500,65} |
+---------------+
and I'd need to replace values putting before each number "p1_" like this:
+------------------------+
| maintainance |
+------------------------+
| {p1_12541,p1_2,p1_4} |
+------------------------+
| {p1_12,p1_131,p1_9999} |
+------------------------+
| {p1_54} |
+------------------------+
| {p1_1} |
+------------------------+
| {p1_12500,p1_65} |
+------------------------+
can you please suggest me how to write replace command using regular expressions ? Thanks in advance
regexp_replace(col, '[0-9]+', 'p_1\&', 'g')