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

Question

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.

Yes it works is Fiddle. I believe there must be some special character in my data. — Macejkou, Feb 23 '23 at 15:28

score 0 · Accepted Answer · answered Feb 23 '23 at 16:19

0

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

answered Feb 23 '23 at 16:19

klin

112,967
15
204
232

AHA!! Thank you! It was "no brake space" character 160 instead of 32 regular space. Looks like \s doesnt cover that. – Macejkou Feb 23 '23 at 16:50

score 0 · Answer 2 · answered Feb 23 '23 at 16:57

0

replace(variable,chr(160),'_')

Is the way to go.

answered Feb 23 '23 at 16:57

Macejkou

636
3
14
24

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

2 Answers2