Substring is generally enough to intercept strings. For some uncertain lengths, this function cannot be used, but there is still a regularity and can be divided by a certain character.
For example: (This is a url, intercept the last part. Now you want to get - the following part)
At this time, the split_part function is needed, such as:
-- url There are 3 - , the string is divided into 4 parts, and the last part is taken, and the last parameter is 4
select split_part(fs.cdn_url ,'-', 4) from file_store fs
What should I do if the number of splitters is different? Here we need to calculate the number of splitters
-- Replace 1 delimiter with 2 delimiters, and then subtract the length to get the number of delimiters
-- The last +1 is to get the last part of the content
select split_part(fs.cdn_url ,'-', length(replace(fs.cdn_url,'-','--')) - length(fs.cdn_url) + 1) from file_store fs
Supplement: PostgreSQL string intercept replacement
Initialize the database
CREATE TABLE public.t1 ( name text ) name ------- "David" "Peter" "Task 2016-09-10 10:09:00" "Task 2016-10-10 12:03:00" "Task 2016-12-22 14:10:00"
Replace the record containing "task" with "Job"
update t1 set name= 'Job'||substring(name,3) where substring(name,position('Task' in name),2)='Task';
select * from t1;
name
-------
"David"
"Peter"
"Job2016-09-10 10:09:00"
"Job2016-10-10 12:03:00"
"Job2016-12-22 14:10:00"
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.