SQL function to remove extra multiple spaces from string

Here is a very efficient solution for removing multiple spaces from a string. I mean replacing multiple spaces between words in a string with a single space, without using a loop. The technique is very simple, I will just run a small script to show how it works.

DECLARE @str AS VARCHAR(50)
SET @str = 'Lets   jot some  code'
PRINT @str

SET @str = REPLACE(@str,' ','{}')
PRINT @str
SET @str = REPLACE(@str,'}{','')
PRINT @str
SET @str = REPLACE(@str,'{}',' ')
PRINT @str

And here is the output.

Lets   jot some  code
Lets{}{}{}jot{}some{}{}code
Lets{}jot{}some{}code
Lets jot some code

Below is a function based on this technique.

CREATE FUNCTION RemoveSpaces
(
-- =============================================
-- Author:      Amit Singh
-- Description: Remove extra spaces from string
-- Usage:       SELECT dbo.RemoveSpaces('Code  Jotter')
-- =============================================
	@str AS VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	RETURN
	REPLACE(REPLACE(REPLACE(@str,' ','{}'),'}{',''),'{}',' ')
END
GO
Advertisements

About Amit Singh

Software engineer; crazy about music, astronomy and radio controlled aircrafts. Big science/technology buff!
This entry was posted in Programming, SQL and tagged , , , , , , , , , , , . Bookmark the permalink.

11 Responses to SQL function to remove extra multiple spaces from string

  1. John McEwen says:

    Wow! what an idea ! What a concept ! Beautiful .. Amazing

  2. Dee says:

    Brilliant, love it, thanks for sharing

  3. Sam says:

    Absolutely brilliant stuff!!

  4. Chintan Gandhi says:

    Lovely ! Superb

  5. Adam says:

    Ooooooooooo So Nice.
    A kick yourself moment (but ok I probably wouldn’t have thought of that)

  6. Simon says:

    Great stuff Amit. Very cool lateral thinking !

  7. Daniel Mann says:

    Most helpful, thank you. This could be done outside of SQL with a regex match and replace, but that didn’t work with a vanilla SQL 2008 R2 instance. Your approach works great.

    Regular Expression (regex) reference:
    replace [ +] (one or more space characters)
    with [ ] (one space character)

    • In an Oracle database, you can use regular expressions (even in SQL), the function would be:
      REGEXP_REPLACE (‘Lets jot some code’, ‘() {2,}’, ‘ ‘)

      Your RemoveSpaces function runs 3 times over the String the REGEXP_REPLACE only one time (but with a much more complex algorithm) so I cannot say what is faster.

  8. Dear Sir, this is modafreakin’ brilliant. Cheers!

  9. Rashmi says:

    Wow!!! Amazing concept….Thank u

  10. hieuhotrung says:

    It is wonderful solution.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s