Remove the middle of a string between wildcards

Question

Remove the middle of a string between wildcards

ncraigg

SSC Veteran

Points: 271
More actions
October 18, 2019 at 10:02 pm

#3690331

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

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

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

DesNorton SSC-Insane Points: 24278 More actions · Answer 1

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)

How to post data/code on a forum to get the best help.

Steve Collins SSCrazy Points: 2068 More actions · Answer 2

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;
go
create table test(
  jsonpayloadnvarchar(max),
  otherint);

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

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

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

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

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

This reply was modified 4 years, 6 months ago by Steve Collins.

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

ncraigg SSC Veteran Points: 271 More actions · Answer 3

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!