Follow Now on Insta

SQL function to remove extra multiple spaces from string

Hey Guys do you know how to remove extra spaces between words in a string? How to remove extra spaces at the beginning or end of the sentence/word? This article will give you the solution for this.

Remove extra spaces between words

 



How do I remove multiple spaces in a string in SQL?

String = '  Hello Readers help me    eliminating    extra spaces'

SQL Query:

 

SELECT '  Hello Readers help me    eliminating    extra spaces' old_text,
          RTRIM(
          LTRIM(
          REPLACE(
          REPLACE(
          REPLACE('  Hello Readers help me    eliminating    extra spaces'  ,' ','{}'),'}{',''),'{}',' ')
                  )
                  )new_text
  FROM dual;

Output:

how to remove extra whitespaces from sql string

This will help you removing extra spaces between words, trailing spaces before and after the sentences.

In above query, you can see the sentence '  Hello Readers help me    eliminating    extra spaces ' has extra spaces at the start and in between words. The above sentence is displayed in old text and the corrected text is displayed in new text.

 

Following code has done the trick 

RTRIM(
          LTRIM(
          REPLACE(
          REPLACE(
          REPLACE('  Hello Readers help me    eliminating    extra spaces'  ,' ','{}'),'}{',''),'{}',' ')
                  )
                  )new_text

We will understand how do it worked. There are 3 REPLACE has been used and then one LTRIM and one RTRIM is used. Although RTRIM was not required but I have used to make your job easier.

Inner most REPLACE clause

Inner most REPLACE is replacing every spaces by '{}' curly braces.

So, after this REPLACE our sentence would be like this

'{}{}Hello{}Readers{}help{}me{}{}{}{}eliminating{}{}{}{}extra{}spaces'

 SELECT
          REPLACE('  Hello Readers help me    eliminating    extra spaces'  ,' ','{}')new_text
  FROM dual;
OUTPUT:
'{}{}Hello{}Readers{}help{}me{}{}{}{}eliminating{}{}{}{}extra{}spaces'

 

Middle wala REPLCAE clause

Now when we add 2nd REPLACE, then result would be like this

'{}Hello{}Readers{}help{}me{}eliminating{}extra{}spaces'

What does it do? It has replaced all occurrences of '}{' with no space.

 '{}{}Hello{}Readers{}help{}me{}{}{}{}eliminating{}{}{}{}extra{}spaces'

 So, it becomes
'{}Hello{}Readers{}help{}me{}eliminating{}extra{}spaces'.

  SELECT
          REPLACE(
          REPLACE('  Hello Readers help me    eliminating    extra spaces'  ,' ','{}'),'}{','') new_text
  FROM dual;

Output: {}Hello{}Readers{}help{}me{}eliminating{}extra{}spaces

 

Outer wala REPLCAE clause

Now comes to 3rd REPLACE, it just replacing all the {} with single space.And then our sentence becomes like this ' Hello Readers help me eliminating extra spaces'. Now there is one extra space at beginning of the sentence. 

 

SELECT
          REPLACE(
          REPLACE(
          REPLACE('  Hello Readers help me    eliminating    extra spaces'  ,' ','{}'),'}{',''),'{}',' ')
  FROM dual;
 

Output:

'Hello Readers help me eliminating extra spaces'

LTRIM is used to remove space from front. RTRIM is used to remove space from last. 

This way it works. If you have doubt then comment below.


😁😁😁😁😁NEED MORE SQL TIPS???? 😁😁😁😁😁😁😁😁😁😁😁😁😁

Visit below link 

👇👇👇👇👇👇👇👇👇

SQL Related Interview Questions and Tips


Post a Comment

1 Comments

Please wait, we will address your query shortly