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

  • Sedler

    SSC Rookie

    Points: 34

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

  • ScottPletcher

    SSC Guru

    Points: 98483

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Phil Parkin

    SSC Guru

    Points: 244656

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

    a) Original text and

    b) Desired text?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Sedler

    SSC Rookie

    Points: 34

    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>

  • ScottPletcher

    SSC Guru

    Points: 98483

     

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply