Replacing strings

  • I have the following string:

    James Murphy;#278;#Leslie Owens;#275;#Rita Sallam;#279;#Stephen Powers;#277;#Susan Feldman;#68

    I need to replace ";#278;#" with ","

    replace ";#275;#" with ","

    replace ";#279;#" with ","

    replace ";#277;#" with ","

    replace ";#68" with ","

    In other words, I need to replace a pattern with ";#" + number + ";#"

    but the last pattern does not have ";#"

    How do I do this?

  • This might help you:

    SET @STR = 'James Murphy;#278;#Leslie Owens;#275;#Rita Sallam;#279;#Stephen Powers;#277;#Susan Feldman;#68'

    --CHAR(59) is a ; CHAR(35) is #

    SET @STR = REPLACE(@Str,CHAR(59) + CHAR(35),',')

    WHILE PATINDEX('%[0-9]%',@Str) > 0

    BEGIN

    SET @Len = LEN(@Str)

    SET @T = SUBSTRING(@Str,PATINDEX ( '%[0-9]%' , @STR),PATINDEX ( '%[0-9]%' , @STR)+1)

    IF @T = 0

    BREAK

    ELSE

    SET @STR = REPLACE(@Str,@T,'')

    CONTINUE

    END

    SET @STR = REPLACE(@Str,',,',',')

    SELECT @STR AS 'Final'

    Giving you:

    James Murphy,Leslie Owens,Rita Sallam,Stephen Powers,Susan Feldman,

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Hi BitBucket,

    That did help. I would have to change it a little, but thank you much for your time. I appreciate it.

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

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