How to get only part of word from column and remove everything before and after it using PostgreSQL-CodePudding

I have the following details column, with varying parameters. How can I get only joblib values? "The Place of joblib is not always the same, so I may bot be able to use substring count"

date:01/12/2014--**--joblib:[snbsd]--**--date_type:no_date--**--max_feat_values:ss,group_filters:[]--**--no_imp_phrases:1--**--variable_facets:auto

date:01/12/2014--**--joblib:[jinxthin]--**--date_type:no_date--**--max_feat_values:ss,group_filters:[]--**--no_imp_phrases:1--**--variable_facets:auto

date:01/12/2014--**--joblib:[snbserv]--**--date_type:no_date--**--max_feat_values:ss,group_filters:[]--**--no_imp_phrases:1--**--variable_facets:auto

date:01/12/2016--**--joblib:[sql12server]--**--date_type:no_date--**--max_feat_values:ss,group_filters:[]--**--no_imp_phrases:1--**--variable_facets:auto

date:01/12/2015--**--joblib:[stfmbinserx]--**--date_type:no_date--**--max_feat_values:ss,group_filters:[]--**--no_imp_phrases:1--**--variable_facets:auto

date:01/12/2011--**--joblib:[ftplikes]--**--date_type:no_date--**--max_feat_values:ss,group_filters:[]--**--no_imp_phrases:1--**--variable_facets:auto

Desired result:

snbsd
jinxthin
snbserv
sql12server
stfmbinserx
ftplikes

CodePudding user response：

Here You go:

substring(substring(var1, position('joblib:' in var1) 8), 1, position(']' in substring(var1, position('joblib:' in var1) 8))-1)

replace var1 with column name containing Your string With below You can try it out directly on PostgreSQL:

WITH myconstants (var1) as (
   values ('date:01/12/2014--**--joblib:[snbsd]--**--date_type:no_date--**--max_feat_values:ss,group_filters:[]--**--no_imp_phrases:1--**--variable_facets:auto')
)
SELECT substring(substring(var1, position('joblib:' in var1) 8), 1, position(']' in substring(var1, position('joblib:' in var1) 8))-1)  
FROM myconstants