2

I have this function (TRIM_REPLACE) that gets spaces from both the right and the left of a string. However, I am trying to modify it so that it trims in the middle also but I would rather combine the function than to do it separately.

Example: Let's say I have a name 

Input
--------------------------
Peter<space><space>Griffin

<space> indicate one blank space in the above input.

I would like to trim the additional space in the so that it looks like this:

Output
--------------------------
Peter<space>Griffin

As you can see that the multiple spaces are replaced with a single space.

CREATE FUNCTION dbo.TRIM_REPLACE
(
    @STRING     VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
    BEGIN
        RETURN LTRIM(RTRIM(@STRING))  + REPLACE(@STRING, '  ',' ')  
    END
GO

How do I accomplish this?

Asynchronous
  • 3,917
  • 19
  • 62
  • 96

1 Answers1

9

If you are only concerned about pairs of spaces, you can use . . .

ltrim(rtrim(replace(@String, '  ', ' ')))

If you might have multiple spaces, you need to put this into a loop:

while charindex('  ', @string) > 0
begin
    set @string = replace(@string, '  ', ' ');
end;
return ltrim(rtrim(@string));
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Good answer, but I don't think you need to loop based on this documentation: http://msdn.microsoft.com/en-us/library/ms186862.aspx. Looks like it does a global replace by default. – Marc May 05 '12 at 18:07
  • Hello, I tested the above example, it seem the LOOP is need to move multiple spaces, Example: I added a work to the column: Peter------Griggin and without the Loop, it returns: Peter----Griggin, only one space is removed. Using the Loop returns: Peter-Griffin. – Asynchronous May 05 '12 at 18:18
  • @Unaverage, Your comment is clear enough but I believe you meant "Griffin" in all cases (as opposed to "Griggin" in the first two and "Griffin" in the last) – RonnBlack Sep 04 '14 at 18:36