前
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
期望的
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
我可以通過使用 split (location ' ') 拆分位置字串并洗掉少于 1 個字符且偏移量為 0 的單詞然后使用 string_agg 將字串重新組合在一起來實作這一點。
但是我確信有一個更簡單的 regexp_replace 解決方案。
uj5u.com熱心網友回復：
在下面使用
select id, regexp_replace(location, r'^\w ','') location
from t1
order by 1
帶輸出
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/485557.html
上一篇：如何加快SELECTSQL查詢？