October 9, 2009 at 10:49 am
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?
October 9, 2009 at 1:17 pm
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,
October 9, 2009 at 5:33 pm
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