?
Solved

NEED SQL QUERY

Posted on 2012-04-12
3
Medium Priority
?
355 Views
Last Modified: 2012-04-12
I have a column like below, i need query which will pick up Char after event number dot(.) still before ;
i.e after Event17063. so first char will be "M" and take still "t".
result will be MSSQLSERVER.TMFSQL.MSSQLSERVER.Alert and should grouped by this title.

Server Name : xxxxx; APP: Connectr; ShortDesc: Event17063.MSSQLSERVER.TMFSQL.MSSQLSERVER.Alert;  Desc:...
Server: qeettdd; SourceApp:Connectr; ShortDesc: Event208.SQLSERVERAGENT.ppgTIPSR.SQLJobFailure.Alert;  Desc: ...
Server Name : abc; APP: Connectr; ShortDesc:..........
Event17063.MSSQLSERVER.Application.ppgTIPSR.Error50000Severity16State1.Alert;  Desc: .....................
Server Name : xxxxx; APP: Connectr; ShortDesc: Event17063.MSSQLSERVER.TMFSQL.MSSQLSERVER.Alert;  Desc:...

Expected output:
Title                                                count
MSSQLSERVER.TMFSQL.MSSQLSERVER.Alert                         2
SQLSERVERAGENT.ppgTIPSR.SQLJobFailure.Alert                   1
MSSQLSERVER.Application.ppgTIPSR.Error50000Severity16State1.Alert       1
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 12

Expert Comment

by:Anuradha Goli
ID: 37836536
The logic behind to retrieve data from such text is given below:
Order is my sample table with fulldata as column with long text .
DECLARE @INDEX1 INT 
DECLARE @INDEX2 INT 
DECLARE @INDEX3 INT 
DECLARE @STRING1 NVARCHAR(200)
DECLARE @STRING2 NVARCHAR(200) 
DECLARE @STRING3 NVARCHAR(200) 

SELECT @INDEX1 = Charindex('EVENT', fulldata) 
FROM   dbo.[ORDER] 
SELECT @STRING1 = Substring(fulldata, @INDEX1, ( Len(fulldata) - @INDEX1 + 1 )) 
FROM   dbo.[ORDER] 

SELECT @INDEX2 = Charindex('.', @STRING1) 
FROM   dbo.[ORDER] 
SELECT @STRING2 = Substring(@STRING1, @INDEX2 + 1, 
                  ( Len(@STRING1) - @INDEX2 + 1 )) 
FROM   dbo.[ORDER] 

SELECT @INDEX3 = Charindex(';', @STRING2) 
FROM   dbo.[ORDER] 
SELECT @STRING3 = Substring(@STRING2, 0, @INDEX3) 
FROM   dbo.[ORDER] 

SELECT @STRING3 

Open in new window


Hope this helps you retrieve data from your fields.
Just logic is to get data between first "." after EVENTxxxxx and before ";"
0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 2000 total points
ID: 37837655
Replace #Events with your table name and "EventDescription" with your column name in below query:

SELECT SUBSTRING (SUBSTRING (EventDescription, PATINDEX ('%Event[0-9]%', EventDescription), CHARINDEX (';  Desc:', EventDescription) - PATINDEX ('%Event[0-9]%', EventDescription)),
      CHARINDEX('.', SUBSTRING (EventDescription, PATINDEX ('%Event[0-9]%', EventDescription), CHARINDEX (';  Desc:', EventDescription) - PATINDEX ('%Event[0-9]%', EventDescription))) + 1, 1000),
      COUNT (*)
FROM #Events
WHERE PATINDEX ('%Event[0-9]%', EventDescription) > 0
And CHARINDEX (';  Desc:', EventDescription) > 0
Group by SUBSTRING (SUBSTRING (EventDescription, PATINDEX ('%Event[0-9]%', EventDescription), CHARINDEX (';  Desc:', EventDescription) - PATINDEX ('%Event[0-9]%', EventDescription)),
      CHARINDEX('.', SUBSTRING (EventDescription, PATINDEX ('%Event[0-9]%', EventDescription), CHARINDEX (';  Desc:', EventDescription) - PATINDEX ('%Event[0-9]%', EventDescription))) + 1, 1000)

Thanks,
Harish
0
 
LVL 5

Author Closing Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 37840504
Excellent job!!!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question