Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replacing strings Expand / Collapse
Author
Message
Posted Friday, October 9, 2009 10:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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?
Post #800953
Posted Friday, October 9, 2009 1:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:17 PM
Points: 5,332, Visits: 25,261
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

Before posting a performance problem please read
Post #801040
Posted Friday, October 9, 2009 5:33 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Hi BitBucket,
That did help. I would have to change it a little, but thank you much for your time. I appreciate it.
Post #801129
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse