How can I replace any year from 1990-2050 with a space ?
I can replace any 4 digit number as follows
select regexp_replace('sdfg 2000', '(\y(\d{4})\y)', '', 'g');
But how additionally I can check the range? Any help are welcome
3 Answers
I have discovered an alternative way to solve your problem. Take a look.
You wish to replace year from 1990-2050. Let's break that range into
1990-19992000-20492050
All three ranges can be matched by following regex.
Regex: [1][9][9][0-9]|[2][0][0-4][0-9]|2050
Explanation:
[1][9][9][0-9]will match years from1990to1999.[2][0][0-4][0-9]will match years from2000to2049.2050will match2050literally|means alteration. It will check either of these three patterns.
2 Comments
Bart Friederichs
This can be simpler:
199[0-9]|20[0-4][0-9]|2050.You can use a CASE expression to extract and test for the year and only replace if the year falls into the range you want:
with test_data (col1) as (
values ('sdfg 2000'), ('foo 1983'), ('bar 2010'), ('bla 1940')
)
select col1,
case
when nullif(regexp_replace(col1, '[^0-9]+',''),'')::int between 1990 and 2050
then regexp_replace(col1, '\d{4}', '', 'g')
else col1
end as replaced
from test_data;
Results in:
col1 | replaced
----------+---------
sdfg 2000 | sdfg
foo 1983 | foo 1983
bar 2010 | bar
bla 1940 | bla 1940
The nullif(..) is necessary for values that do not contain any numbers. If you don't have values like that, you can leave it out.
Comments
You can't, from Wikipedia (emphasis mine):
Each character in a regular expression (that is, each character in the string describing its pattern) is understood to be: a metacharacter (with its special meaning), or a regular character (with its literal meaning).
In your case, the letters do not have literal meaning, their meaning depends on characters around it.
1and2is possible. But other places cannot be restricted in that way. Better look for some way with programming.