Help, so close with this script yet... needs to be tweaked (using Replace)

  • 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')

  • 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".

  • Can you please clarify exactly what you want by providing an example showing

    a) Original text and

    b) Desired text?


  • 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>

  •  

    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