PostgreSQL replace range of unicode chars

Question

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?

dnoeth · Accepted Answer · 2016-03-17 14:34:01Z

5

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...

edited Mar 17, 2016 at 14:34

answered Mar 16, 2016 at 16:37

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Ruth Over a year ago

thanks @dnoeth, that helps a little. do you know a way to set a range of codes? I tried that out but it does not work (eg. U&'[\0041-\0043]')

dnoeth Over a year ago

@RuthiRuth: There's no way to specifiy a range in TRANSLATE, but there's REGEXP_REPLACE. I modify my answer...

Ruth Over a year ago

I tried that but then I get only the first occurrence in "myproperty" and not all occurences...

Ruth Over a year ago

I got it, I have to set the flag 'g' to replace it with all occurrences. if you could edit your answer @dnoeth I can mark it as right :)

dnoeth Over a year ago

@RuthiRuth: Thanks, every DBMS has it's own rules regarding regex. Added it to my answer...

|

Collectives™ on Stack Overflow

PostgreSQL replace range of unicode chars

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related