Remove the middle of a string between wildcards

  • ncraigg

    SSC Veteran

    Points: 271

    I have a field called 'jsonpayload' in a table called 'auditlog' that is populated with activity from logged on users of a website.  Part of the payload includes the permalink for the page of the site (such as /randompermalink).  The permalink can be any number of characters into the string, but always comes between "dealPermaLink:" and ",Id".  Some of the permalinks have started coming through with Google Analytics tracking codes, so instead of /randompermalink, I get /randompermalink?_ga1234567890.  The Google codes can be quite lengthy and are always a variable number of characters.  How can I delete everything in the record between "dealsPermaLink: /randompermalink" and "Id", so that "............dealsPermaLink: /randompermalink?_ga1234567890,Id............" becomes "............dealsPermaLink: /randompermalink,Id............"?

  • DesNorton


    Points: 22931

    This might help ...

    SELECT jsonpayload = STUFF(jsonpayload, Start.Pos, Fin.Pos -1, '')
    FROM YourTable
    CROSS APPLY (SELECT CHARINDEX('?', jsonpayload, PATINDEX('%dealsPermaLink: /%', jsonpayload))) AS Start(Pos)
    CROSS APPLY (SELECT PATINDEX('%,Id%', SUBSTRING(jsonpayload, Start.Pos, LEN(jsonpayload)))) AS Fin(Pos)
  • scdecade

    SSChasing Mays

    Points: 646

    If 'jsonpayload' is valid JSON it could be updated in place.  Using Sql 2016+ something like this (copy, paste, run) could work:

    /* create a test table with json column and other cols... */
    drop table if exists test;
    create table test(
    jsonpayload nvarchar(max),
    other int);

    /* insert sample data */
    insert test(jsonpayload, other)
    (select '/randompermalink?_ga1234567890' dealsPermaLink, 456 Id for json path, without_array_wrapper) jsonpayload,
    12 other;

    /* BEFORE */
    select 'Before', * from test;

    /* the piece of json */
    @json nvarchar(max)=(select jsonpayload from test);
    /* the specific object */
    @link nvarchar(max)=json_value(@json, N'strict $.dealsPermaLink');
    /* remove chars after ? */
    @fixed nvarchar(max)=left(@link, charindex('?', @link)-1);

    /* update test table with corrected object */
    update test
    jsonpayload=json_modify(@json, N'strict $.dealsPermaLink', @fixed);

    /* AFTER */
    select 'After', * from test;


    • This reply was modified 4 months ago by  scdecade.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • ncraigg

    SSC Veteran

    Points: 271

    Many thanks to you both for replying.  @scdecade, it is valid json, but unfortunately I'm working with SQL 2012.

    @desnorton, I'll give that a try today.  Again, thanks so much to both of you for taking the time to help!

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

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