I have a table and want update query to update dob
------------
dob
------------
19101984
19111984
19071985
19081985
19101985
19121985
I want to remove sub string from position 5 to character length of 2 means in the result table will be :
------------
dob
------------
191084
191184
190785
190885
191085
191285
Use regex_replace:
select regexp_replace(dob::text, '(\d{4})\d{2}(\d{2})', E'\\1\\2')
from your_table;
For updating:
update your_table
set dob = regexp_replace(dob, '(\d{4})\d{2}(\d{2})', E'\\1\\2');
The regex pattern being used here is:
(\d{4})\d{2}(\d{2})
^^^^ ^^^^ <-- capture groups
The first four and last two digits are captured in groups 1 and 2, respectively. Then, we simply replace with the first and second capture group.
It is not clear whether the dob column is text or not. If it is numeric, then you should be able to cast to text and run the above query.
dob column is numeric. If so, then to update as you want you would need to do a double cast. I won't update my answer further unless you tell us this is the case. In any event, I would argue that column should really be text anyway.regexp_replace(dob, '(\d{4})\d{2}(\d{2})', E'\1\2') ... i.e. single backslashes for the capture groups. I simply used the Postgres documentation as a pattern for my answer, I'm surprised there are warning at all.