November 9, 2004 at 6:21 pm
Example is a string like 'a b c d'. Is there a function available that will return 'abcd'?
November 9, 2004 at 7:29 pm
declare @STR varchar(20)
set @STR = 'a b c d'
select @STR as orig_string, replace(@str, ' ', '') as no_spaces
Regards
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 10, 2004 at 10:06 am
Thanks Phil.
But I guess I should have added that replace seems to work when the db is in 80 compatibility mode but not in 65. Any other suggestions? Thanks in advance!
November 11, 2004 at 6:49 am
could this do the job (never worked with 65 compatibility)
CREATE FUNCTION [dbo].[fnReplace] (@String as varchar(8000), @Search as char(1), @Replace as varchar(1))
RETURNS varchar(8000) AS
BEGIN
Declare @i as int
Declare @Length as int
Declare @Char as char(1)
Declare @Result as varchar(8000)
set @Result = ''
set @i = 0
set @Length = LEN(@String)
while @i <= @Length
begin
set @Char = substring(@String, @i, 1)
if @Char = @Search
begin
set @Result = @Result + @Replace
end
else
begin
set @Result = @Result + @Char
end
set @i = @i + 1
end
Return @Result
END
GO
select dbo.FnReplace ('a b c d gt r',' ','')
= 'abcdgtr'
November 11, 2004 at 9:02 am
Try using PATINDEX.
declare @cndx int;
declare @text varchar(8000);
declare @pattern varchar(50);
declare @replace varchar(50);
set @text = 'a b c d ';
set @pattern = '% %'; -- wildcard pattern
set @replace = '';
set @cndx = patindex(@pattern, @text);
while @cndx > 0
begin
if len(@replace) > 0
begin
set @text = left(@text, @cndx - 1) + @replace + substring(@text, @cndx + 1, len(@text)-@cndx);
end
else
begin
set @text = left(@text, @cndx - 1) + substring(@text, @cndx + 1, len(@text)-@cndx);
end;
set @cndx = patindex(@pattern, @text);
end;
print @text;
November 11, 2004 at 3:16 pm
User defined functions were not available for SQL 6.5. PatIndex may work.
Quand on parle du loup, on en voit la queue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply