Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

Extract data from nvarchar column

The case: 
We have a log table that include Message column (nvarchar).
We need to extract from the message some data, for example: Entity ID and Entity Type.



SELECT LogId,
(case when CHARINDEX ('EntityID:', Msg) > 0
then substring ( Msg, 
CHARINDEX ('EntityID:', Msg) + 9, -- 9=length of 'EntityID:'
( case when CHARINDEX ('EntityID:', Msg) > 0 
then ( CHARINDEX (',', Msg, CHARINDEX ('EntityID:', Msg)) 
- CHARINDEX ('EntityID:', Msg) - 9) -- 9=length of 'EntityID:'
else CHARINDEX ('EntityID:', Msg) + 9 -- 9=length of 'EntityID:'
end
)
)
else NULL
end ) as EntityID,
(case when CHARINDEX ('EntityType:', Msg) > 0
then substring ( Msg, 
CHARINDEX ('EntityType:', Msg) + 11, -- 11=length of 'EntityID:'
( case when CHARINDEX ('EntityType:', Msg) > 0 
then ( CHARINDEX (',', Msg, CHARINDEX ('EntityType:', Msg)) 
- CHARINDEX ('EntityType:', Msg) - 11) -- 11=length of 'EntityID:'
else CHARINDEX ('EntityType:', Msg) + 11 -- 11=length of 'EntityID:'
end
)
)
else NULL
end ) as EntityType
FROM OurLogTable

No comments:

Post a Comment