Remove Special Character from string
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[RemoveSpChar]
(
-- Add the parameters for the function here
@sInput varchar(MAX)=''
)
RETURNS varchar(MAX)
AS
BEGIN
-- Declare the return variable here
DECLARE @sOutput Varchar(MAX),
@iIndex int,
@iLength int,
@sChar varchar(1),
@iASCII int,
@iLen int,
@iRem int
set @sInput= ltrim(rtrim(@sInput))
set @iLength = len(@sInput)
set @iIndex =1
set @sOutput=''
while @iIndex <= @iLength
begin
set @sChar=substring(@sInput,@iIndex,1)
set @iASCII=ascii(@sChar)
if ((@iASCII>=48 and @iASCII<=57) or (@iASCII>=65 and @iASCII<=90) or (@iASCII>=97 and @iASCII<=122) )
set @sOutput=@sOutput+@sChar
--return @sChar + ' - ' + convert(varchar,@iASCII)
set @iIndex =@iIndex +1
end
if len(@sOutput)>17
set @sOutput=substring(@sOutput,1,17)
else if len(@sOutput)<6
begin
set @iLen=len(@sOutput)
set @iRem=6-@iLen
set @sOutput=substring(@sOutput + replicate('0',@iRem),1,6)
end
-- Return the result of the function
RETURN @sOutput
END