Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How can I extract specific data in SQL that are in between """": in column A into column B?

I’m trying to extract every data in my column that are in between "" "": here are sample data:

Column A
{""Activity"":""ConnectFromExternalApplication"",""Id"":""xz[we-654231-aja"",""RecordType"":20,""CreationTime"":""2024-02-13T17:59:44"",""Operation"":""ConnectExternal"",""OrganizationId"":""[REDACTED]"",""UserType"":0,""UserKey"":""bb0-649-429-a53f-b6328"",""Workload"":""BI"",""ResultStatus"":null,""UserId"":""bb0-16-4219-a53f-665328"",""ClientIP"":null,""CustomData"":""""}
{""Id"":""f120-00c3-4ddb-ba60-c9ed83df0"",""RecordType"":20,""CreationTime"":""2023-03-13T17:46:14"",""Operation"":""ViewReport"",""OrganizationId"":""[REDACTED]"",""UserType"":0,""UserKey"":""100300009C22A108"",""Workload"":""PowerBI"",""UserId"":""[email protected]"",""ClientIP"":""213.174.203.7"",""Activity"":""ViewReport"",""ItemName"":""Opt Dashboard"",""WorkSpaceName"":""UK Operational Reports"",""DatasetName"":""Operational Dashboard"",""ReportName"":""Operational Dashboard"",""CapacityId"":""82EF1AA9-0554-4E95-B249-15F"",""CapacityName"":""BI UK Capacity 1"",""WorkspaceId"":""6588ee54-5b73-441d-a3ac-f133f"",""AppName"":""UK Operational Reports"",""ObjectId"":""Operational Dashboard"",""DatasetId"":""1cd1ce2f-9395-4be0-9521-3e38e7e236b8"",""ReportId"":""76d769fd-a9ec-4c77-ac3d-7cd57d9449ca"",""ArtifactId"":""76d769fd-a9ec-4c77-ac3d-7cd57d9449ca"",""ArtifactName"":""Opt Dashboard"",""IsSuccess"":true,""ReportType"":""PowerBIReport"",""RequestId"":""a2214fde-dffc-7c30-083c-6702b7"",""ActivityId"":""f61bed2a-5906-425c-8048-682a779900aa"",""AppReportId"":""e0ab7628-73b5-4832-9161-c966996d293e"",""DistributionMethod"":""Apps"",""ConsumptionMethod"":""Power BI Web"",""AppId"":""e44d1e27-1d9d-4585-a477-fb66fcd"",""ArtifactKind"":""Report""}

value such as Activity, DatasetId, RecordType anything that are in between "" "": needs to be extracted and place in one column

Column B
Activity, ConnectFromExternalApplication, Id, RecordType, CreationTime, Operation, …, CustomData
Id, …, ArtifactKind
Tags:

2

Answers


  1. 0

    Which environment are you on? if it is in ORACLE you can use REPLACE to remove the double quotes and then fetch the data

    Login or Signup to reply.
  2. 0 
    SELECT REGEXP_REPLACE(JSON_KEYS(REPLACE(ColumnA, '""', '"')), '[\[\]\" ]', '')
FROM test;
    REGEXP_REPLACE(JSON_KEYS(REPLACE(ColumnA, ‘""’, ‘"’)), ‘[[]" ]’, ”)
    Id,UserId,UserKey,Activity,ClientIP,UserType,Workload,Operation,CustomData,RecordType,CreationTime,ResultStatus,OrganizationId
    Id,AppId,UserId,AppName,UserKey,Activity,ClientIP,ItemName,ObjectId,ReportId,UserType,Workload,DatasetId,IsSuccess,Operation,RequestId,ActivityId,ArtifactId,CapacityId,RecordType,ReportName,ReportType,AppReportId,DatasetName,WorkspaceId,ArtifactKind,ArtifactName,CapacityName,CreationTime,WorkSpaceName,OrganizationId,ConsumptionMethod,DistributionMethod

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search