Trimming all spaces from a string

[theninja]

Hi, i want to cut all spaces within a string and then count the number of characters within that string. The srting can contain any number of spaces.

ie 'The cat ran over the road'
all i want to do is trim it to
'thecatranovertheroad' and then simply count the values.

is there an easy way to do this ?

Thanks in advance Ü

 

[karluk]

To remove spaces:

select replace('The cat ran over the road',' ','')

To get the length of the resulting string:

select len(replace('The cat ran over the road',' ',''))

 

[bassguy]

Hi this is a stored proc that will do it



create proc trimmer

@mystring varchar(100)
as
declare @mystringlength int
declare @lefttrim varchar(50)
declare @stringlength int
declare @cut int

set @mystring=ltrim(rtrim(@mystring))
set @stringlength=0

while charindex(' ',@mystring)>0
begin
set @mystringlength=len(@mystring)
set @cut=charindex(' ',@mystring)
set @lefttrim=left(@mystring,(@cut-1))
set @mystring=right(@mystring,(@mystringlength-@cut))
set @stringlength=@stringlength+len(@lefttrim)
end
set @stringlength=@stringlength+len(@mystring)
select @stringlength as length



hope that helps

bassguy