July 31, 2020 at 3:54 pm
I am trying to update URL's that are in our Jira environment, but they are in the middle of a description field
I need to alter the ending of all links that have a ".doc?" in the middle of them, I am trying the below script, but the issue is there is additional text after the link itself which is being removed with the following script. No 2 links end the same way so I need to use a wildcard, but how do I tell the system to not replace the words/text after the link itself? Any help would be HUGE as this script has me pulling out hair. I should say I am not a DBA and I am not a coder, I write scripts based off others I have worked with as a System Engineer.. I know enough to get me in trouble as they say 🙂
Thank you for any help in advance
update jiraissue
set Jiraissue.DESCRIPTION = cast(left(CAST(Jiraissue.DESCRIPTION AS NVARCHAR(MAX)), patindex('%.doc?%',CAST(Jiraissue.DESCRIPTION AS NVARCHAR(MAX)))+4)+'?web=1]'as ntext)
From Jiraissue
JOIN project on project.ID = Jiraissue.project
And project.pkey = 'TM'
And jiraissue.DESCRIPTION like '%.doc?%'
and issuenum in ('59626')
July 31, 2020 at 6:04 pm
Not 100% sure what you are ultimately trying to do, but maybe something like this:
...
SET DESCRIPTION = REPLACE(CAST(DESCRIPTION AS nvarchar(max)), N'.doc?', N'?web=1')
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 31, 2020 at 6:21 pm
Can you please clarify exactly what you want by providing an example showing
a) Original text and
b) Desired text?
July 31, 2020 at 7:14 pm
absolutely guys
Here is an example i used recently
The Entire text of the Description Field that I am trying to update might look like this
Hello Everyone,
Here is the new link you asked about
Hyperlink[https://OldURL/Site/location.doc?andnowuneededstuff]
Please make sure to check it out
The New Description field needs to say
Hello Everyone,
Here is the new link you asked about
Hyperlink[https://NEWURL/Site/location.doc?web=1]
Please make sure to check it out
____________________________________________________________________________________________
I am able to change the first part with a replace but the 2nd part is proving to be difficult for me
update jiraissue
Set Jiraissue.DESCRIPTION = CAST(REPLACE(CAST(Jiraissue.DESCRIPTION AS NVARCHAR(MAX)),'https://OldURL/Site/location,'Https://NEWURL/Site/location')AS NTEXT)
From Jiraissue
JOIN project on project.ID = Jiraissue.project
And project.pkey = 'TM'
And jiraissue.DESCRIPTION like '%https://OldURL/Site/location%'
The 2nd part of this to change the End I have gotten this far
update jiraissue
set Jiraissue.DESCRIPTION = cast(left(CAST(Jiraissue.DESCRIPTION AS NVARCHAR(MAX)), patindex('%.doc?%',CAST(Jiraissue.DESCRIPTION AS NVARCHAR(MAX)))+4)+'?web=1]'as ntext)
From Jiraissue
JOIN project on project.ID = Jiraissue.project
And project.pkey = 'TM'
And jiraissue.DESCRIPTION like '%.doc?%'
the issue is that the result I am getting ends up like this
Hello Everyone,
Here is the new link you asked about
Hyperlink[https://NEWURL/Site/location.doc?web=1] <Everything after this is also removed>
July 31, 2020 at 10:06 pm
DROP TABLE IF EXISTS #testdata;
CREATE TABLE #testdata ( description nvarchar(max) NULL, new_description nvarchar(max) NULL );
INSERT INTO #testdata ( description) VALUES
('Hyperlink[https://OldURL/Site/location.doc?andnowuneededstuff]'),
('Hyperlink[https://OldURL/Site/location.docquestionmarkmissing]')
--as a SELECT
SELECT
description,
REPLACE(STUFF(description, start_of_doc, end_of_doc - start_of_doc, '.doc?web=1'), 'OldURL', 'NEWURL') AS new_description
FROM #testdata
CROSS APPLY (
SELECT CHARINDEX('.doc?', description) AS start_of_doc
) AS ca1
CROSS APPLY (
SELECT CHARINDEX(']', description, start_of_doc + 1) AS end_of_doc
) AS ca2
WHERE start_of_doc > 0
--as an UPDATE
UPDATE t
SET new_description = REPLACE(STUFF(description, start_of_doc, end_of_doc - start_of_doc, '.doc?web=1'), 'OldURL', 'NEWURL')
--SET description = REPLACE(STUFF(description, start_of_doc, end_of_doc - start_of_doc, '.doc?web=1'), 'OldURL', 'NEWURL')
FROM #testdata t
CROSS APPLY (
SELECT CHARINDEX('.doc?', description) AS start_of_doc
) AS ca1
CROSS APPLY (
SELECT CHARINDEX(']', description, start_of_doc + 1) AS end_of_doc
) AS ca2
WHERE start_of_doc > 0
SELECT *
FROM #testdata
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply