none
Remove spaces

    Question

  • Trying to remove the space between 2 last names.

    Sample Last name is Johnson Avery, I want the output to JohnsonAvery.  Tried the function dsi_fnremovechars but that did not work.  What else would be good for this?

     

    Thursday, January 30, 2014 8:45 PM

Answers

  • Alter Function dbo.dsi_fnremovechars(@ReplaceList varchar(10),@String varchar(100))
    returns varchar(100)
    as
    
    
    BEGIN
    
    DECLARE
    
    @PuncCount AS INT
    
    SET
    
    @PuncCount =Case @ReplaceList When ' ' Then DATALENGTH(@ReplaceList) Else   LEN(@ReplaceList) End
    
    
    WHILE
    
    @PuncCount != 0
    
    SELECT  @String = REPLACE(@String, SUBSTRING(@ReplaceList,@PuncCount,1), ''),
    
    @PuncCount
    
    = @PuncCount - 1
    
    
    RETURN
    
      @String
    
    
    END

    LEN() return 0 for whitespace.
    Thursday, January 30, 2014 9:08 PM

All replies

  • Select Replace('Johnson Avery',' ','')

    Thursday, January 30, 2014 8:47 PM
  • The above is for a specific item.  I am needing for a specific field that could affect 2000+ rows. 

    I had this dbo.dsi_fnremovechars(@stripchars,abc.abcnamelast)

    Thursday, January 30, 2014 8:50 PM
  • The above is for a specific item.  I am needing for a specific field that could affect 2000+ rows. 

    I had this dbo.dsi_fnremovechars(@stripchars,abc.abcnamelast)


    You have to provide us the code inside dbo.dsi_fnremovechars.
    Thursday, January 30, 2014 8:52 PM
  • BEGIN

    DECLARE

    @PuncCount AS INT

    SET

    @PuncCount = LEN(@ReplaceList)



    WHILE

    @PuncCount != 0

    SELECT  @String = REPLACE(@String, SUBSTRING(@ReplaceList,@PuncCount,1), ''),


    @PuncCount

    = @PuncCount - 1



    RETURN

      @String



    END

    Thursday, January 30, 2014 8:59 PM
  • Alter Function dbo.dsi_fnremovechars(@ReplaceList varchar(10),@String varchar(100))
    returns varchar(100)
    as
    
    
    BEGIN
    
    DECLARE
    
    @PuncCount AS INT
    
    SET
    
    @PuncCount =Case @ReplaceList When ' ' Then DATALENGTH(@ReplaceList) Else   LEN(@ReplaceList) End
    
    
    WHILE
    
    @PuncCount != 0
    
    SELECT  @String = REPLACE(@String, SUBSTRING(@ReplaceList,@PuncCount,1), ''),
    
    @PuncCount
    
    = @PuncCount - 1
    
    
    RETURN
    
      @String
    
    
    END

    LEN() return 0 for whitespace.
    Thursday, January 30, 2014 9:08 PM
  • In fact you can replace the LEN using DATALENGTH.
    Thursday, January 30, 2014 9:09 PM
  • Just change the len(@ReplaceList) with the code given below

    SET @PuncCount = LEN(@String)

    -Prashanth

    Thursday, January 30, 2014 9:15 PM
  • Why a simple REPLACE not working?

    SELECT REPLACE(LastName,' ','') as LastName FROM yourtable


    Thursday, January 30, 2014 9:22 PM