How do I strip all spaces from within a string?

  • Example is a string like 'a b   c    d'.  Is there a function available that will return 'abcd'?

  • declare @STR varchar(20)

    set @STR = 'a b c d'

    select @STR as orig_string, replace(@str, ' ', '') as no_spaces

    Regards

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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!

  • 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'

  • 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;

  • 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