Tuesday, February 2, 2010

Remove Special Character from XML string + sql Server

Remove Special Character from string 

-- =============================================
-- Author:       
-- Create date:
-- 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

No comments:

Post a Comment