How can I remove an expression giving a word and a start and end character
I am trying to remove an expression from a text giving a word and a start and end character. For example:
http://removecharacter.com?key=keyvalue&product=productid&user=userid
http://exampleremove.com?id=keyvalue&prodID=productid&login=userid
I want to remove productid
for all my urls (parameter name is not the same for all urls, but this is the parameter value).
So my urls will be:
http://removecharacter.com?key=keyvalue&user=userid
http://exampleremove.com?id=keyvalue&login=userid
Giving word productid
and separator &
and &
. I have played with CHARINDEX
, LEFT
and RIGHT
functions, but without any success
source to share
Try the following:
DECLARE @t TABLE(url NVARCHAR(1000))
INSERT INTO @t VALUES
('http://removecharacter.com?key=keyvalue&product=productid&user=userid'),
('http://exampleremove.com?id=keyvalue&prodID=productid&login=userid'),
('http://exampleremove.com?id=keyvalue&login=userid')
;WITH cte AS(SELECT *,
REVERSE(url) AS newurl,
CHARINDEX('&ditcudorp', REVERSE(url)) pt
FROM @t WHERE CHARINDEX('&ditcudorp', REVERSE(url)) > 0)
UPDATE cte SET url = REVERSE(STUFF(newurl, pt, CHARINDEX('&', newurl, pt + 1) - pt, ''))
SELECT * FROM @t
Output:
url
http://removecharacter.com?key=keyvalue&user=userid
http://exampleremove.com?id=keyvalue&login=userid
http://exampleremove.com?id=keyvalue&login=userid
In cte
you change url
and find the index '%&ditcudorp%'
. Then you replace all characters in the position between the index '%&ditcudorp%'
and the index of the next with '%&%'
an empty char.
source to share