Post reply

Nested REPLACE functions - How to Maintain

  • September 3, 2004 at 12:59 pm

    #59940

    Hello.

    I'm using nested replace functions to remove characters from a field.  The Characters are not contiguous so I can't concatenate the target charcters in the arguements of the field. 

    For Example:

    replace((replace(replace (replace(Item.description, char(10),''), char(13), ''), char(151), char(45))),char(09),'')

    My question to the group is, does anybody have any suggestions as to how I can format this code to impove it's readability and add comments?

    Secondary question, anybody have any functions or routines to strip out HTML formating characters from text?

     

    Thanks

    jgl

  • September 6, 2004 at 3:34 am

    #521841

    Go Up the Chain! Get the application layer to strip out the junk data from the inputs before saving it to your database.  Use a Regular Expression, or HTMLEncode functions (depending on your apps programming language)

    You could however create a user function to do the striping of the data.

    CREATE FUNCTION dbo.stripChars (@badString varchar(200))

    AS

    BEGIN

           SET @badString = replace(@badstring, char(10), '')

           SET @badString = replace(@badstring, char(9), '')

           SET @badString = replace(@badstring, char(45), '')

           SET @badString = replace(@badstring, char(151), '')

           RETURN @badString

    END

    Then you can

    SELECT dbo.stripChars(mycolumnwithbaddata) FROM mytable

    Julian Kuiters
    juliankuiters.id.au

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply