IF EXISTS
  (SELECT *
     FROM SYS.[objects] SO
    WHERE SO.[name] = 'KEYHM_FN_RemoveSpuriousWhitespace'
      AND SO.[type] = 'FN')
BEGIN
  DROP FUNCTION [dbo].[KEYHM_FN_RemoveSpuriousWhitespace]
END
GO

CREATE FUNCTION
       [dbo].[KEYHM_FN_RemoveSpuriousWhitespace]
      (@original VARCHAR(MAX),
       @maxlen   INT)
RETURNS VARCHAR(5000)
AS
/*******************************************************************************************************
* Removes newlines, line feeds, non-breaking spaces and multiple spaces from the input				   *
*   string in order to produce an output format suitable for display in list.                          *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KEYHM_FN_RemoveSpuriousWhitespace]			                           *
* Copied from			: [dbo].[ky_RemoveSpuriousWhitespace2]				                           *
*                                                                                                      *
* Modification History	:                                                                              *
* 2022-06-02	Vignesh M		Created                                                                *
 *******************************************************************************************************/
BEGIN
  DECLARE @len int

  SET @original = ISNULL(@original, '')
  IF LEN(@original) > (2 * @maxlen)
    BEGIN
      SET @original = SUBSTRING(@original, 1, (2 * @maxlen))
    END

  SET @original = REPLACE(@original, char(10), ' ')
  SET @original = REPLACE(@original, char(13), ' ')
  SET @original = REPLACE(@original, char(160), ' ')

  SET @original = RTRIM(@original)
  
  SET @len = 0
    WHILE (@len <> LEN(@original))
      BEGIN
        SET @len = LEN(@original)
        SET @original = REPLACE(@original, '  ', ' ')
      END

  RETURN SUBSTRING(@original, 1, @maxlen)
  
END
GO