Remove empty spaces in a String in TSQL

Vimalsoft Pty Ltd

Good Day All i have the Following string in a Field

'This
is the

reason that i did this>> '

i want to remove spaces in TSQL to this

'This is the reason that i did this>>'

THanks

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa[at]dotnetfunda.com http://www.Dotnetfunda.com

Blue_Boy

here it is

declare @NewLine char(2)
set @NewLine=char(13)+char(10)

select rtrim(ltrim(Replace('This
is the

reason that i did this>> ',@NewLine,'')))

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.

Vimalsoft Pty Ltd

You are a Star thanks :)

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa[at]dotnetfunda.com http://www.Dotnetfunda.com

PIEBALDconsult

I wrote a CLR function to do that, compresses whitespace to a single SPACE:

namespace PIEBALD.Lib.LibExt.Compress
{
public static partial class LibExt
{
public static string
Compress
(
this string Victim
)
{
System.Text.StringBuilder result = new System.Text.StringBuilder ( Victim.Length ) ;

  bool first = true ;

  for ( int i = 0 ; i < Victim.Length ; i++ )
  {
    if ( !System.Char.IsWhiteSpace ( Victim \[ i \] ) )
    {
      result.Append ( Victim \[ i \] ) ;

      first = true ;
    }
    else if ( first )
    {
      result.Append ( ' ' ) ;

      first = false ;
    }
  }

  return ( result.ToString() ) ;
}

}
}

CREATE ASSEMBLY Library FROM 'C:\bin\Library.Sql.dll'
GO
CREATE FUNCTION dbo.Compress(@Source NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME Library.[PIEBALD.Lib.LibExt.Compress.LibExt].Compress;
GO

Blue_Boy

You are welcome. :)

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.

PIEBALDconsult

That might not be what you want if the string is 'hello\r\nworld'.

RobLazarus

Here's a generic example:

create table t (s sysname)
insert into t select 'this is the reason that I did this. '

-- convert tabs to spaces
update	t set s = replace(s, '	',' ')
where	charindex('	', s) > 0

-- now do the work.
while 1=1
begin
	update t
	set		s = substring(s, 1, charindex('  ', s, 1)-1) + ' ' + ltrim(substring(s,charindex('  ', s, 1), 8000))
	where	charindex('  ', s, 1) > 0

	if @@rowcount = 0
		break
end

select	s
from	t