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) A socialist is someone who will give you the shirt off *someone else's* back.
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply