Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using REPLACE with a string where I need to replace %anystring% with some new string


Using REPLACE with a string where I need to replace %anystring% with some new string

Author
Message
SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2798 Visits: 3681
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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2798 Visits: 3681
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
roryp 96873
roryp 96873
SSC Eights!
SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)

Group: General Forum Members
Points: 843 Visits: 6597
Does this not work for you?


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


GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2798 Visits: 3681
@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
rVadim
rVadim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 2299
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 %.
roryp 96873
roryp 96873
SSC Eights!
SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)

Group: General Forum Members
Points: 843 Visits: 6597
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.
SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2798 Visits: 3681
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
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search