October 18, 2019 at 10:02 pm
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............"?
October 19, 2019 at 5:55 am
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)
October 19, 2019 at 1:29 pm
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;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 20, 2019 at 12:51 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply