无法使用regexp_replace或replace函数从PostgreSQL字符串中删除所有空白字符。

huangapple go评论72阅读模式
英文:

Not able to remove all whitespace characters in PostgreSQL string using regexp_replace or replace functions

问题

Variable value: Snímek obrazovky 2019-12-05 v 13.33.37.png

regexp_replace(variable, '\s', '_', 'g')
Returns: Snímek_obrazovky_2019-12-05_v_13.33.37.png(请注意"_v"后面没有空格)

replace(variable, ' ', '_')
Returns: Snímek_obrazovky_2019-12-05_v_13.33.37.png = 相同。

我不知道还可以尝试什么。有任何想法吗?可能在其中有一些特殊字符?可以确定是哪种特殊字符。

英文:

I am having werid problem. Trying to remove whitespace characters from varchar variable in PostgresSQL. However one space is not removed. I dont know how is this possible.

Variable value: Snímek obrazovky 2019-12-05 v 13.33.37.png

regexp_replace(variable, '\s', '_', 'g')

Returns: Snímek_obrazovky_2019-12-05_v 13.33.37.png (notice a space after _v)

replace(variable, ' ', '_')

Returns: Snímek_obrazovky_2019-12-05_v 13.33.37.png = the same.

I dont know what else to try. Any ides? Might be some special character in there? Can identify which kind.

答案1

得分: 0

在UTF8编码中,您可以检查字符串中所有字符的代码:

select ch, ascii(ch) as code
from unnest(regexp_split_to_array('Snímek obrazovky 2019-12-05 v 13.33.37.png', '')) as ch
英文:

In UTF8 encoding you can check the codes of all characters in the string:

select ch, ascii(ch) as code
from unnest(regexp_split_to_array('Snímek obrazovky 2019-12-05 v 13.33.37.png', '')) as ch

答案2

得分: 0

replace(variable, chr(160), '_') 是正确的方式。

英文:
replace(variable,chr(160),'_')

Is the way to go.

huangapple
  • 本文由 发表于 2023年2月23日 23:08:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/75546654.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定