Search code examples
regexreplaceposixamazon-redshift

Replace spaces in between single characters in Redshift (POSIX regex...ie no lookarounds)

I am trying to delete whitespace in between single characters at the end of a string.

For example I would need this:

SOME COMPANY L L C

to become this:

SOME COMPANY LLC

The catch is that this is in Redshift, which only supports POSIX regular expressions, so I cannot use a lookahead (?=) which would make life too easy.

The closest thing I have so far is:

(\s.{1,3}$)

See here: https://regexr.com/3h81p

However this selects the letters too. I can't just replace the letters with LC because there are many more filings such as L P, L C, N A, so it would be very nice to just replace the whitespace between them.

Any help appreciated!

Solution

  • Search for:

    (\s\w)\s(\w)\s?(\w?)$

    Replace with:

    $1$2$3

    This should work for removing spaces between two singled-out characters or three singled-out characters at the end of a line.