a regexp_replace solution tfor stripping the first word or character from a sentence if its only cha-CodePudding

BEFORE

with t1 as 
(
select           1 id,"1 university of washington,           seattle, washington" location
union all select 2 id,"a university of washington, seattle  , washington"
union all select 3 id,"b university of washington,      , washington"
union all select 4 id,"university of washington, seattle , washington"
union all select 5 id,"d university of new york,ny  , usa"
union all select 6 id,"university of new york,new york  , usa"

)
select * from t1
order by 1

DESIRED

with t2 as 
(
select           1 id,"university of washington,           seattle, washington" location
union all select 2 id,"university of washington, seattle  , washington"
union all select 3 id,"university of washington,      , washington"
union all select 4 id,"university of washington, seattle , washington"
union all select 5 id,"university of new york,ny  , usa"
union all select 6 id,"university of new york,new york  , usa"

)
select * from t2
order by 1

I can achieve this by splitting the location string up using split (location ' ') and removing words less then 1 character long with an offset of 0 and then using string_agg to put the string back together.

However I'm sure there is a simpler regexp_replace solution.

CodePudding user response：

use below

select id, regexp_replace(location, r'^\w ','') location
from t1
order by 1

with output