How do I combine trim and replace functions to remove spaces anywhere in the string?
sql, sql-server-2008, t-sql
Solution
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));
Problem
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?