Geeks With Blogs
Gaurav Taneja Great dreams... never even get out of the box. It takes an uncommon amount of guts to put your dreams on the line, to hold them up and say, "How good or how bad am I?" That's where courage comes in.
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 Posted on Tuesday, April 1, 2008 9:38 PM SQL SERVER | Back to top


Comments on this post: Remove Special Character from XML string + sql Server

# re: Remove Special Character from XML string + sql Server
Requesting Gravatar...
Nice function... it doesnt compile ( sql syntax errors)
Left by Pat on Apr 02, 2008 10:45 AM

# re: Remove Special Character from XML string + sql Server
Requesting Gravatar...
Thanks, worked like a charm on my Sql 2005 box.
Left by Chris on Nov 25, 2008 10:39 AM

# re: Remove Special Character from XML string + sql Server
Requesting Gravatar...
Thanks, for the script.

how do you use the function? For example, how do you remove ! and :?
Left by Dave on Jan 13, 2009 1:31 AM

Your comment:
 (will show your gravatar)


Copyright © Gaurav Taneja | Powered by: GeeksWithBlogs.net