如何用postgres中的空替换非ascii字符 [英] how to replace non ascii charactes with empty in postgres

问题描述

嗨 

 我对postgresql有一个疑问，如何在postgresql中用空值替换非ascii字符

表：Emp

地址
$ b $bÎlt-t-Fce

Ä€ddÄ«rkÊ¿¥
$ b $bʿAlūla



基于以上数据我想要输出如下 

Ilt-t-Fce

AddAArkEay

EAlAla



我试过如下 

SELECT替换（地址，'％[^ a-Z0-9，] ％'，''）

FROM emp

地址如'％[^ a-Z0-9，]％'

或 

选择   替换（地址，'^ [^ [：ascii：]]'，''）来自  emp其中 地址〜'^ [^ [：ascii：]]' 

以上两个查询未给出预期结果

你可以告诉我如何用postgresql中的空值替换非ascci chareater的查询。

解决方案

嗨BalaKrishna，

感谢您的反馈。我相信这不能用单一命令来处理。你需要创建一个函数来处理这个问题。

你可以参考下面的StackOverFlow线程。我相信这些可能对你有帮助。

https://stackoverflow.com/questions/36176985/remove-ascii-extended-characters-128-onwards-sql / 36177685＃36177685

https://stackoverflow.com/questions/15259622/how-do-i-remove-extended-ascii-字符来自-s-string-in-t-sql

希望它有所帮助。

Hi 
 I have one doubt in postgresql, how to replace non ascii character with empty values in postgresql
table :Emp
address
Îlt-t-Fce
Āddī Ārkʿay
ʿAlūla

based on above data i wantoutput like below 
Ilt-t-Fce
AddAArkEay
EAlAla

I tried like below 
SELECT replace(addres,'%[^a-Z0-9, ]%',' ')
FROM emp
WHERE address like '%[^a-Z0-9, ]%'
or 
select   replace(addres,'^[^[:ascii:]]',' ') from  emp where  address ~ '^[^[:ascii:]]' 

above two queris are not giving expected result
can you please tell me how to write query to replace for non ascci chareater with empty values in postgresql

解决方案

Hi BalaKrishna,

Thank for your feedback. I believe this cannot be handled with single command. You need create a function to take care of this.

Could you please refer below StackOverFlow threads. I believe these might help you.

https://stackoverflow.com/questions/36176985/remove-ascii-extended-characters-128-onwards-sql/36177685#36177685

https://stackoverflow.com/questions/15259622/how-do-i-remove-extended-ascii-characters-from-a-string-in-t-sql

Hope it helps.

这篇关于如何用postgres中的空替换非ascii字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！