PostgreSQL replace range of unicode chars

I have some problems with characters not allowed in XML-export. The request is simply to remove the invalid characters from the text to ensure corrext export/import of data.

so far, thanks to this post I have an sql statement which replaces one declared Unicode char:

UPDATE mytable SET myproperty = replace(myproperty, U&'\UNICODE', '');

It removes the specified Unicode from all occurrences inside the defined cell. But I would like to give a bunch of Unicode chars to be replaced, eg. 0x1 - 0xf Does anybody have an idea how to achieve that?

Answers


Use TRANSLATE to remove multiple characters:

TRANSLATE(myproperty, 'list of characters', '');

Normally it's used to replace characters, but if the replacement string is empty it's removing them.

Edit:

Of course you can also use a regular expression, this allows ranges of characters:

regexp_replace(myproperty, '[\u0041-\u0043a]', '', 'g')

Adding the 'g' flag to replace all occurrences...


Need Your Help

Which (batch) language to learn for merging sourcefiles?

javascript macros batch-file makefile visual-studio-macros

I'm creating a Greasemonkey/UserScript script. Because of the Greasemonkey sandbox I have to keep everything in one file but at 5k+ lines, maintenance is beginning to become rather difficult.

PHP : Delayed database request also without live page

php session delay countdown

So for example I click a button what will send something to the database after 1 minutes, but I want to send it also if I leave the page :)