0

I want to use regexp_replace to replace all blank with '_'.

I use this statment:

select regexp_replace('"<div_class="CCL-temp-border"><div_class="input-group_moveDivEnd"_style="margin-bottom:_5px;_top:_auto;_left:_auto;_width:_100%;_position:_relative;_opacity:_1;_filter:_none;"_data-id="moveDivEnd_1545116285310">_; <span_class="input-group-addon_CCL-te (...)"', '\s', '_', 'g')

But the result is this:

"<div_class="CCL-temp-border"><div_class="input-group_moveDivEnd"_style="margin-bottom:_5px;_top:_auto;_left:_auto;_width:_100%;_position:_relative;_opacity:_1;_filter:_none;"_data-id="moveDivEnd_1545116285310">_;_______<span_class="input-group-addon_CCL-t (...)"

My statment is this:

select case when length(topiccontent)=0 THEN '_' else coalesce(regexp_replace(replace(replace(replace(topiccontent,chr(13), '_'),chr(10),'_'),' ','_'),'\s', '_', 'g'),'_') end as topiccontent from ccl_topics

You can see the blank still exists, why?

enter image description here

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Add you tried to use `( )` instead of `\s`? – z3nth10n Jan 15 '19 at 04:27
  • @z3nth10n Yes,I tried,there is no effect! –  Jan 15 '19 at 04:29
  • It would help if you included the actual statements you have tried so far – harmic Jan 15 '19 at 04:36
  • @harmic I added all the attempts,please see my code. –  Jan 15 '19 at 04:45
  • Your first query seems to be working, see [this](https://dbfiddle.uk/?rdbms=postgres_9.4&fiddle=770227c5c0b7b766974b33942413311f).Are you sure it's a space between `t` and `(` not any other unknown character? – Kaushik Nayak Jan 15 '19 at 05:04
  • I see it with notepad,it doesn't look like any other unknown character. –  Jan 15 '19 at 05:15
  • Did you verify your "blank" is a basic Latin space character (Unicode: U+0020 / ASCII 32) - or even member of the character class `[[:space:]]` (shorthand `\s`)? The character I see in your question is, but the original may have been lost in translation. Whitespace can be tricky. See: https://stackoverflow.com/a/22701212/939860 – Erwin Brandstetter Jan 15 '19 at 05:30

1 Answers1

0

I know why it can't be replaced.

There are some character restrictions when the data is pasted out from database.

The omitted part is converted to (...).

So (...) is not real characters, but ellipses.

For example, more than 600 characters exist in a column of a table, and then paste it,the result with ellipsis marks.