Removing spaces between words in sql

I guess there is no built in functions to do this but I have a function that replaces anything that is not A-Z with a space and returns @.data. What I additionally need the function to do is scrunch up @.data (remove all blanks betwwen each word so that 'I ran very fast' would be 'Iranveryfast').

What I need help in doing is the "Scrunch" part. Is there a way I could move the @.Data to something like @.DataHold and inspect each character, if it is not a blank, move that character back to @.Data?
This was pretty easy for me to do in C# with a while loop, but I do not know how to get it done in SQL Server 2005.

Thanks for any help!

Use the replace function.

replace( @.Data, ' ', '' )

For example,

DECLARE @.Data varchar(1000)
SET @.Data = 'I ran very fast'

SELECT replace( @.Data, ' ', '' )

Iranveryfast

removing duplicate rows

Hi,
Please give the DML to SELECT the rows avoiding the duplicate rows. Since there is a text column in the table, I couldn't use aggreate function, group by (OR) DISTINCT for processing.
Table :
create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(4, 'dbc')
go
Please advise,
Thanks,
Smithanot very efficient - and prone to possible truncation of col2 -

select distinct col1, cast(col2 as varchar(8000)) from test|||Thanks. I need the output to be with the same datatype, since I need to create temp tables using the selected data(using SELECT INTO)|||again not very efficient:

select temp.col1, cast(temp.newcol2 as text) col2
into newtable
from
(select distinct col1, cast(col2 as varchar(8000)) newcol2 from test) temp

Removing all end of line character from a string

Hello all,

Im looking for an efficient way to remove all end of line character from a string.

Is there a function to do that or to replace them with another character?

This should help you out:

declare @.string varchar(100),

@.CrLf varchar(2)

set @.String = 'line

with a break'

print @.string

set @.CrLf = char(13) + char(10) -- carriage return + line feed

set @.string = replace(@.string, @.CrLf, '')

print @.string

regards Gert-Jan