我正在尝试解析SQL Server中的JSON数据。列标题是大小写混合的。有没有一种方法可以将其转换为大写或小写。下面是代码:
SELECT
pr.AuditEvent_Id as [AuditEvent_HttpHeaders_Id]
,[AuditEvent_HttpHeaders_ContentType]
,[AuditEvent_HttpHeaders_Accept]
,[AuditEvent_HttpHeaders_AcceptEncoding]
,[AuditEvent_HttpHeaders_AcceptLanguage]
,[AuditEvent_HttpHeaders_Authorization]
,[AuditEvent_HttpHeaders_Host]
,[AuditEvent_HttpHeaders_Referer]
,[AuditEvent_HttpHeaders_UserAgent]
,[AuditEvent_HttpHeaders_Origin]
FROM dbo.Audit_Resource pr
CROSS APPLY OpenJson(pr.HttpHeaders) WITH (
[AuditEvent_HttpHeaders_ContentType] varchar(255) '$."content-Type"',
[AuditEvent_HttpHeaders_Accept] varchar(255) '$.accept',
[AuditEvent_HttpHeaders_AcceptEncoding] varchar(255) '$."accept-Encoding"',
[AuditEvent_HttpHeaders_AcceptLanguage] varchar(255) '$."accept-Language"',
[AuditEvent_HttpHeaders_Authorization] varchar(255) '$.authorization',
[AuditEvent_HttpHeaders_Host] varchar(255) '$.host',
[AuditEvent_HttpHeaders_Referer] varchar(255) '$.referer',
[AuditEvent_HttpHeaders_UserAgent] varchar(255) '$."user-Agent"',
[AuditEvent_HttpHeaders_Origin] varchar(255) '$.origin',)
例如:在某些情况下'$。“ content-Type”'是'$。“ Content-Type”'。如何使其不区分大小写。
SELECT
pr.AuditEvent_Id as [AuditEvent_HttpHeaders_Id]
,[AuditEvent_HttpHeaders_ContentType]
,[AuditEvent_HttpHeaders_Accept]
,[AuditEvent_HttpHeaders_AcceptEncoding]
,[AuditEvent_HttpHeaders_AcceptLanguage]
,[AuditEvent_HttpHeaders_Authorization]
,[AuditEvent_HttpHeaders_Host]
,[AuditEvent_HttpHeaders_Referer]
,[AuditEvent_HttpHeaders_UserAgent]
,[AuditEvent_HttpHeaders_Origin]
FROM dbo.Audit_Resource pr
CROSS APPLY ( values ( replace(
replace(
replace(
replace(pr.HttpHeaders,'Content-Type','content-type')
,'Accept','accept')
,'accept-Encoding','accept-encoding')
,'accept-Language','accept-language') -- ... Other Keys
) )J(S)
CROSS APPLY OpenJson(J.S) WITH (
[AuditEvent_HttpHeaders_ContentType] varchar(255) '$."content-Type"',
[AuditEvent_HttpHeaders_Accept] varchar(255) '$.accept',
[AuditEvent_HttpHeaders_AcceptEncoding] varchar(255) '$."accept-Encoding"',
[AuditEvent_HttpHeaders_AcceptLanguage] varchar(255) '$."accept-Language"',
[AuditEvent_HttpHeaders_Authorization] varchar(255) '$.authorization',
[AuditEvent_HttpHeaders_Host] varchar(255) '$.host',
[AuditEvent_HttpHeaders_Referer] varchar(255) '$.referer',
[AuditEvent_HttpHeaders_UserAgent] varchar(255) '$."user-Agent"',
[AuditEvent_HttpHeaders_Origin] varchar(255) '$.origin',)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句