Solved

NEED SQL QUERY

Posted on 2012-04-12
3
341 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
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 500 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now