SQL Clone
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
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13870 Visits: 3697
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58747 Visits: 9730
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
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13870 Visits: 3697
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
roryp 96873
roryp 96873
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1667 Visits: 6611
Does this not work for you?


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


GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58747 Visits: 9730
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
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13870 Visits: 3697
@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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
rVadim
rVadim
Default port
Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)

Group: General Forum Members
Points: 1433 Visits: 2308
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1667 Visits: 6611
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.
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13870 Visits: 3697
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18119 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