2

Our Item Description field can have a random number of spaces between text. I use RTRIM and LTRIM to clean the right and left sides of the column when pulling with SQL, but this doesn't address the unnecessary spaces between words.

I tried creating a Formula TRIM({Command.ItemDesc}) but this has not removed the extra spaces. Is there a way to address this in Crystal or SQL 2016?

4444
  • 3,541
  • 10
  • 32
  • 43
Derek Alsup
  • 21
  • 1
  • 5
  • The solutions presented here are applicable: https://stackoverflow.com/questions/2182877/sql-method-to-replace-repeating-blanks-with-single-blanks – Phil Dennis Aug 29 '17 at 19:42
  • Do you wish to remove *all* spaces in the middle? Or just reduce multiple chained spaces into a single space? That will affect which answers work for your case. – 4444 Aug 29 '17 at 21:13
  • I am trying to reduce the number of chained spaces into a single space. So far, it seems as if all answers reduce a specific number of spaces into a single space. The problem is that the number of spaces varies from record to record. – Derek Alsup Aug 31 '17 at 13:14

2 Answers2

2

TRIM does not deal with whitespace in the middle of string, only the whitespace before and after the string, you need to use Replace function, try:

Replace({Command.ItemDesc}," ","")
LONG
  • 4,490
  • 2
  • 17
  • 35
0

LONG's answer will remove all spaces from your string. If you wish to keep certain necessary spaces, but remove unnecessary ones, you can use this formula:

TRIM(Replace({Command.ItemDesc},"  "," ")

This formula has two steps:

  1. Remove all spaces before and after the string
  2. Reduce any spots with multiple spaces into one space only
4444
  • 3,541
  • 10
  • 32
  • 43