Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Using REPLACE with a string where I need to...
Add to briefcase
22 posts, Page 1 of 3
123»»»

Using REPLACE with a string where I need to replace %anystring% with some new string Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
SQLKnowItAll
Posted Monday, August 13, 2012 10:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 2,614, Visits: 3,130
Hi everyone,

I am actually fairly new to using the REPLACE function and don't know if I can use regex with it. The string that I want to replace is always between 2 % characters and includes the % characters.

Sample data:
SELECT 'somestring+%sometext%' AS string
INTO #temp
UNION ALL
SELECT '%sometext%'
UNION ALL
SELECT 'somestring+%sometext%+somesecondstring'

So I would like the results when selected from the tale to look like this (no update needed, this is just a query):
SELECT 'somestring+replaced' AS newString
UNION ALL
SELECT 'replaced'
UNION ALL
SELECT 'somestring+replaced+somesecondstring'

So, can I use the REPLACE function to find the '%whatever%' string?

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1344281
GSquared
Posted Monday, August 13, 2012 10:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571
I could be wrong, but I'm pretty sure Replace() won't take regex or other patterns. I'm pretty sure it only deals with strings or string-type variables with pre-defined values.

However, solving this with Stuff() would be quite easy.

SELECT  string,
        CHARINDEX('%', string, 1),
        CHARINDEX('%', string, CHARINDEX('%', string, 1) + 1) - CHARINDEX('%', string, 1),
        STUFF(string, CHARINDEX('%', string, 1),
              CHARINDEX('%', string, CHARINDEX('%', string, 1) + 1) - CHARINDEX('%', string, 1) + 1, 'replaced')
FROM    (SELECT 'somestring+%sometext%' AS string
         UNION ALL
         SELECT '%sometext%'
         UNION ALL
         SELECT 'somestring+%sometext%+somesecondstring') AS Example;

The first three columns are just the internal pieces of the logic. The final column is what you need for this kind of thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1344287
SQLKnowItAll
Posted Monday, August 13, 2012 10:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 2,614, Visits: 3,130
GSquared (8/13/2012)
I could be wrong, but I'm pretty sure Replace() won't take regex or other patterns. I'm pretty sure it only deals with strings or string-type variables with pre-defined values.

However, solving this with Stuff() would be quite easy.

SELECT  string,
        CHARINDEX('%', string, 1),
        CHARINDEX('%', string, CHARINDEX('%', string, 1) + 1) - CHARINDEX('%', string, 1),
        STUFF(string, CHARINDEX('%', string, 1),
              CHARINDEX('%', string, CHARINDEX('%', string, 1) + 1) - CHARINDEX('%', string, 1) + 1, 'replaced')
FROM    (SELECT 'somestring+%sometext%' AS string
         UNION ALL
         SELECT '%sometext%'
         UNION ALL
         SELECT 'somestring+%sometext%+somesecondstring') AS Example;

The first three columns are just the internal pieces of the logic. The final column is what you need for this kind of thing.
Interesting... Thanks G. I think you are right that REPLACE() does not accept patterns. That's why my RegEx wasn't working

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1344290
roryp 96873
Posted Monday, August 13, 2012 10:42 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 4:48 PM
Points: 577, Visits: 4,175
Does this not work for you?

select REPLACE(yourstring '%sometext%', 'replaced')
from #temp
Post #1344293
GSquared
Posted Monday, August 13, 2012 10:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571
Alternatively, if you really need to use Replace instead of Stuff for this, try using Substring to get the piece that you want to replace. Build the Substring parameters the same way I did the Stuff position and length. Then use that as the first argument for the Replace function.

SELECT  string,
        SUBSTRING(string, CHARINDEX('%', string, 1),
                  CHARINDEX('%', string, CHARINDEX('%', string, 1) + 1) - CHARINDEX('%', string, 1) + 1),
        REPLACE(string,
                SUBSTRING(string, CHARINDEX('%', string, 1),
                          CHARINDEX('%', string, CHARINDEX('%', string, 1) + 1) - CHARINDEX('%', string, 1) + 1),
                'replaced')
FROM    (SELECT 'somestring+%sometext%' AS string
         UNION ALL
         SELECT '%sometext%'
         UNION ALL
         SELECT 'somestring+%sometext%+somesecondstring') AS Example;

Either will work. Just thought of the nested Replace(Substring()) version second is all.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1344295
SQLKnowItAll
Posted Monday, August 13, 2012 10:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 2,614, Visits: 3,130
@rory: Sorry I was not clear that sometext could be anything; i.e. %sometext%, %hogwash%, '%palinka%'... So your proposed solution will not work.

@g: No need to use replace, I think your STUFF solution will work just fine. Thanks again!

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1344299
rVadim
Posted Monday, August 13, 2012 10:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, May 19, 2013 1:36 PM
Points: 887, Visits: 2,062
roryp 96873 (8/13/2012)
Does this not work for you?

select REPLACE(yourstring '%sometext%', 'replaced')
from #temp



I think the point is to replace any text between %.
Post #1344300
roryp 96873
Posted Monday, August 13, 2012 10:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 4:48 PM
Points: 577, Visits: 4,175
But based on the desired output he provided it looked like he wanted the % signs removed as well. Or did I misread that?

Edit: Nevermind, I get it now. You won't know what the string is in advance, you are just looking for % and want to replace what is in there. Sorry for the confusion.
Post #1344302
SQLKnowItAll
Posted Monday, August 13, 2012 11:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 2,614, Visits: 3,130
roryp 96873 (8/13/2012)
But based on the desired output he provided it looked like he wanted the % signs removed as well. Or did I misread that?

Edit: Nevermind, I get it now. You won't know what the string is in advance, you are just looking for % and want to replace what is in there. Sorry for the confusion.
Yes, but your were right first with the %'s themselves; i.e. they should also be replaced.

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1344309
dwain.c
Posted Tuesday, August 14, 2012 4:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192
Playing around with PATINDEX, which is the closest thing you'll find to REGEX (except the CLR REGEX functions of course), I come up with something like this:

;WITH SampleData (string) AS (
    SELECT 'somestring+%sometext%'
    UNION ALL
    SELECT '%sometext%'
    UNION ALL
    SELECT CAST('somestring+%sometext%+somesecondstring' AS VARCHAR(100))
    )
SELECT string, StringReplaced=STUFF(string, [First], 1+[Second], 'replaced')
FROM SampleData
CROSS APPLY (SELECT [First]=PATINDEX('%[%]%', string)) a
CROSS APPLY (SELECT [Second]=PATINDEX('%[%]%', SUBSTRING(string, 1+[First], LEN(string)))) b


Not sure if this helps though.

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1344599
« Prev Topic | Next Topic »

Add to briefcase
22 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.