CODEWITHSUNDEEP
regexpostgresql
John
John

Reputation: 634

Remove whitespaces from beginning, between, end

I have the fallowing string ' this is my string ' is it possible to remove all the white spaces from the beginning and the end and just have one space between words.

To select all spaces I've used:

SELECT regexp_replace('   this  is my  string   ', '[ ]{2,}', '', 'g');

The problem with that is the two spaces between words.

Upvotes: 1

Views: 146

Answers (3)

anubhava
anubhava

Reputation: 784948

You can use:

SELECT regexp_replace('   this  is my  string   ', '^ +| +$| +(?= )', '', 'g');

RegEx Demo

This will remove all spaces from:

  1. beginning
  2. end
  3. Will convert multiple spaces in middle to single space

Explanation:

  • ^ +| +$ matches spaces at beginning or at end of the string
  • +(?= ) is a positive lookahead, that matches 1 or more spaces ONLY if it is followed by at least a space
  • Replacement is by empty string.

Upvotes: 3

Avinash Raj
Avinash Raj

Reputation: 174696

Use anchors.

SELECT regexp_replace('   this  is my  string   ', '^ +| +$|( ) +', '\1', 'g');

DEMO

  • ^ + Matches all the leading one or more spaces.
  • | OR
  • <space>+$ Matches all the trailing spaces.
  • | OR (ie, from the remianing string)
  • ( ) + Capture the first space and match all the following spaces.
  • Replacing all the matched spaces with group index 1 will give you the desired output.

Upvotes: 3

Tomalak
Tomalak

Reputation: 338148

SELECT 
trim(both ' ' from regexp_replace('    this  is my  string   ', '  +', ' ', 'g'));

Upvotes: 2

Related Questions