0

I wanted to replace the multi spaces to a single space whenever more than one single space is found in the string. I was using this expression but it is not giving desired results. Is it possible that the data is having some extra non-ascii character than space.

What else am missing

I've tried below solution to update the column with below TRIM( REGEXP_REPLACE ( 'Report   the value of total personnel expense.', '( )+',' ',1 ,0, 'c' ) ) ;

its not giving the desired result.

Actual result:Report   the value of total personnel expense. Desired Result: Report the value of total personnel expense.

user2653353
  • 83
  • 1
  • 1
  • 9
  • Have you looked at this one? https://stackoverflow.com/questions/1981349/regex-to-replace-multiple-spaces-with-a-single-space It's for JS, but the search string may suit you. – ravioli Aug 27 '19 at 05:43
  • Thanks ravioli, tried this TRIM( REGEXP_REPLACE('Report the value of total personnel expense.','[/\s]{2,}',' ',1,0, 'i' ) ), but not working. With normal spaces it is working but i think something else is between "Report" and "the". – user2653353 Aug 27 '19 at 06:49
  • Hmm...can you create a fiddle and share it: http://refiddle.com/ ? – ravioli Aug 27 '19 at 07:32
  • http://refiddle.com/olos – user2653353 Aug 27 '19 at 08:19

2 Answers2

1

[ ]{2,} will catch 2 or more space characters, then replace with a single space.

SELECT RegExp_Replace('Report   the value of total personnel expense','[ ]{2,}',' ');

Output: Report the value of total personnel expense

Karl Anka
  • 2,529
  • 1
  • 19
  • 30
  • Thank you that worked in that example..can you make out whats happening here in this example .. i am not getting the desired result. SELECT 'Part A: Income   1. Interest income' as col1 , RegExp_Replace('Part A: Income   1. Interest income','[ ]{2,}',' '); – user2653353 Aug 27 '19 at 08:37
  • Not sure what your desired result is there? – Karl Anka Aug 27 '19 at 08:39
  • SELECT RegExp_Replace('Part A: Income   1. Interest income','[ ]{2,}',' '); there are three spaces between Income and digit 1. – user2653353 Aug 27 '19 at 08:41
  • I think when we copy and paste from this webpage its working. But in the original table if i run that its not working. So I think some non printable characters on the play. – user2653353 Aug 27 '19 at 08:47
  • i did this is for the the spaces.. two came with ascii value of 32 and another one came as ascii 353 -> select ascii(' '); – user2653353 Aug 27 '19 at 08:58
  • This solution worked for me. REGEXP_REPLACE(col,'[^[:ascii:]]','') and then replace the double spaces by a single space. – user2653353 Aug 27 '19 at 09:22
1

You might try to replace consecutive whitespace with a single blank:

REGEXP_REPLACE (x, '\s+',' ')
--'\s' matches blank, new line, tab, etc.

Or remove additional whitespace:

REGEXP_REPLACE (x, '\s\K\s+','') ) ;
-- '\K' drops the previously matched characters from the match

I prefer #1 because all whitespace is replaced by a blank, while #2 will keep tab/new line if it's the 1st char in a match

dnoeth
  • 59,503
  • 4
  • 39
  • 56