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 123»»»

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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:23 AM
Points: 2,691, Visits: 3,376
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
Posted Monday, August 13, 2012 10:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Monday, August 13, 2012 10:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:23 AM
Points: 2,691, Visits: 3,376
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
Posted Monday, August 13, 2012 10:42 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:25 AM
Points: 690, Visits: 5,922
Does this not work for you?

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

Post #1344293
Posted Monday, August 13, 2012 10:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Monday, August 13, 2012 10:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:23 AM
Points: 2,691, Visits: 3,376
@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
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: Today @ 2:42 PM
Points: 990, Visits: 2,218
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
Posted Monday, August 13, 2012 10:54 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:25 AM
Points: 690, Visits: 5,922
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
Posted Monday, August 13, 2012 11:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:23 AM
Points: 2,691, Visits: 3,376
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
Posted Tuesday, August 14, 2012 4:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:45 PM
Points: 3,617, Visits: 5,237
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.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1344599
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse