如何在PostgreSQL中替换文本列中的字符数组? [英] How to replace an array of characters in a column of text in PostgreSQL?
本文介绍了如何在PostgreSQL中替换文本列中的字符数组?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有2个文本列,需要在更新时将数组1中的字符("q","x","y","z")替换为数组2中的索引等效值("a")','b','c','d').
I have 2 text columns where I need to replace (on update) chars from array 1 ('q','x','y','z') to their index-equivalent value in array 2 ('a','b','c','d').
我最接近atm的是这样将替换调用相互嵌套在一起</p>
The closest I've come to atm is to nest the replace calls within each other like so
UPDATE
mytable
SET
col1=replace(
replace(
replace(
replace(
col1,'q','a'
),'x','b'
),'y','c'
),'z','d'
),
col2=replace(
replace(
replace(
replace(
col2,'q','a'
),'x','b'
),'y','c'
),'z','d'
)
但是肯定有更好的方法可以做到这一点吗?在我的实际情况下,我有14个字符对.如果有相关性,这些字符是日本象形文字和瑞典字母的带重音字母的组合.
but surely there must be a better way to do this? In my live case I have 14 of those char pairs. If it has any relevance - the chars are a mix of japanese hieroglyphs and accented letters from Swedish alphabet.
推荐答案
PostgreSQL have special function for this, translate():
update mytable set
col1 = translate(col1, 'qxyz', 'abcd'),
col2 = translate(col2, 'qxyz', 'abcd')
这篇关于如何在PostgreSQL中替换文本列中的字符数组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文