Post reply

Remove Characters which lies between special Characters

  • March 29, 2011 at 9:31 am

    #389165

    All , I am looking for a t-sql command which gives me best output for the character field i have ,,,

    I have a column which has values as

    1) <XXXX> ABCDEFGHIJKLMNOPQRSTUVWXYZ <XXXXX> ABCGDJGHH < XXXXXX>

    Here i need to get rid of all characters which lies in between < and > and display teh remaing Output can anyone help me out on this

    Thanks

    Rajesh

  • March 29, 2011 at 9:58 am

    #1304992

    Three questions:

    1) Is there a maximum string length?

    2) Is there a maximum number of output elements?

    3) Do you want the spaces before and after the <> symbols trimmed?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • March 29, 2011 at 10:36 am

    #1305014

    Hi Dixie, Thanks for ur reply , I got solution for this , I created a Function , when each time i populated i updated my table using this function

    Create Function StripData(@Input VarChar(8000))

    Returns VarChar(8000)

    As

    Begin

    While CHARINDEX('>', @Input) > CHARINDEX('<', @Input)

    Select @Input = STUFF(@Input,

    CHARINDEX('<', @Input),

    CHARINDEX('>', @Input, CharIndex('<', @Input))-CharIndex('<', @Input)+1,

    '' )

    Return @Input

    End

    You can then use this function for updating data and/or selecting it. However you want...

    Update YourTable

    Set YourTable.ColumnName = dbo.StripData(YourTable.ColumnName)

  • March 29, 2011 at 10:40 am

    #1305020

    What happens when there's a < in the text?

  • March 29, 2011 at 11:14 am

    #1305041

    Thanks for the reply, Rajesh. Glad that's working for you, even though the function has a (shudder) WHILE loop in it. 🙂

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

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