SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Filter out data from a string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ino mart
Starting Member

12 Posts

Posted - 09/24/2012 :  08:07:51  Show Profile  Reply with Quote
All

I have a field where I have to filter out 2 strings.
The field is a summary from log-files and is always based upon a same mask:

TARGET: 'some variable data' RULE NAME: 'some variable data' ITSM Asset: servername - diskname

I can filter out the diskname by using:
REPLACE(RIGHT(dbo.tblTickets.SUMMARY, CHARINDEX('-', REVERSE(dbo.tblTickets.SUMMARY), 2)), '-', '') AS diskname


But can someone give me the code how to filter out the servername?
So, the field always ends with "ITSM Asset: 'servername' - 'diskname'", but the length of servername and diskname is variable.

Regards
Ino

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/24/2012 :  08:27:27  Show Profile  Reply with Quote
SELECT LTRIM(RTRIM(LEFT(STUFF(dbo.tblTickets.SUMMARY,1,CHARINDEX('ITSM Asset:',dbo.tblTickets.SUMMARY)+11,''),
	CHARINDEX('-',STUFF(dbo.tblTickets.SUMMARY,1,CHARINDEX('ITSM Asset:',dbo.tblTickets.SUMMARY)+11,''))-1)))
Go to Top of Page

ino mart
Starting Member

12 Posts

Posted - 09/24/2012 :  08:50:17  Show Profile  Reply with Quote
This outputs next strings.

example:
nap84 RULE NAME Database Availability and Critical States MESSAGE The database status is DOWN. ITSM Asset JDENAP06
VIVP1 RULE NAME Database Availability and Critical States MESSAGE The database status is DOWN. ITSM Asset NCSQAP02
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/24/2012 :  09:04:18  Show Profile  Reply with Quote
quote:
Originally posted by ino mart

This outputs next strings.

example:
nap84 RULE NAME Database Availability and Critical States MESSAGE The database status is DOWN. ITSM Asset JDENAP06
VIVP1 RULE NAME Database Availability and Critical States MESSAGE The database status is DOWN. ITSM Asset NCSQAP02

I am not following what you are saying here. I based it on the rules that you described in your original posting:
quote:
the field always ends with "ITSM Asset: 'servername' - 'diskname'", but the length of servername and diskname is variable.
Based on that,
DECLARE @x VARCHAR(1000)= 
	'TARGET: ''some variable DATA'' RULE NAME: ''some variable data'' ITSM Asset: servername - diskname'

SELECT LTRIM(RTRIM(LEFT(STUFF(@x,1,CHARINDEX('ITSM Asset:',@x)+11,''),
	CHARINDEX('-',STUFF(@x,1,CHARINDEX('ITSM Asset:',@x)+11,''))-1)))
Go to Top of Page

ino mart
Starting Member

12 Posts

Posted - 09/24/2012 :  09:40:54  Show Profile  Reply with Quote
Let me clarify. It's the field summary which always ends with "ITSM Asset: 'servername' - 'diskname'". On that summary-field I need to extract the servername and the diskname

The diskname can be filtered out with the SQL-statement I already posted.
The servername is the SQL-string I am not able to generate. With three examples below, I need the values JDENAP06, NCSQAP02 and CPCDWDRP as output

TARGET: apsnap84 RULE NAME: Database Availability and Critical States MESSAGE: The database status is DOWN. ITSM Asset: JDENAP06 - APSNAP84
TARGET: SHDVIVP1 RULE NAME: Agent unreachable MESSAGE: Failted to connect. ITSM Asset: NCSQAP02 - SHDVIVP1
TARGET: drcconp1 RULE NAME: Listener Availability MESSAGE: The listener is down ITSM Asset: CPCDWDRP - DRCCONP1


Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 09/24/2012 :  09:58:33  Show Profile  Reply with Quote
Borrowing From Sunita

SELECT PARSENAME( REPLACE(SUBSTRING(@x,CHARINDEX('ITSM Asset:',@x)+11,100),' - ','.'),1) as ServerName
,PARSENAME( REPLACE(SUBSTRING(@x,CHARINDEX('ITSM Asset:',@x)+11,100),' - ','.'),2) as DiskName


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/24/2012 :  10:00:39  Show Profile  Reply with Quote
I am afraid I am still not following what the issue is. With the new sample data you posted I get the following:
CREATE TABLE #tmp (Src VARCHAR(1024));

INSERT INTO #tmp VALUES ('TARGET: apsnap84 RULE NAME: Database Availability and Critical States MESSAGE: The database status is DOWN. ITSM Asset: JDENAP06 - APSNAP84');
INSERT INTO #tmp VALUES ('TARGET: SHDVIVP1 RULE NAME: Agent unreachable MESSAGE: Failted to connect. ITSM Asset: NCSQAP02 - SHDVIVP1');
INSERT INTO #tmp VALUES ('TARGET: drcconp1 RULE NAME: Listener Availability MESSAGE: The listener is down ITSM Asset: CPCDWDRP - DRCCONP1');

SELECT LTRIM(RTRIM(LEFT(STUFF(Src,1,CHARINDEX('ITSM Asset:',Src)+11,''),
	CHARINDEX('-',STUFF(Src,1,CHARINDEX('ITSM Asset:',Src)+11,''))-1)))
FROM #tmp;

DROP TABLE #tmp;


-- result
JDENAP06
NCSQAP02
CPCDWDRP
Go to Top of Page

ino mart
Starting Member

12 Posts

Posted - 09/25/2012 :  04:35:55  Show Profile  Reply with Quote
This is neither an option as the account which will run the query only got read-rights. It is not allowed to create tables.

So, I have a table named dbo.tblTickets. The table got next fields:
Date, Agent, Severity and Summary.

The table contains some thousands of records. I need a view which shows those 4 fields and adds two additional fields: ServerName and DiskName.

The values for ServerName and DiskName are in the field Summary and must be extracted from there.

I now have a query with next statement:
SELECT dbo.tblTickets.Date, dbo.tblTickets.Agent, dbo.tblTickets.Severity, dbo.tblTickets.SUMMARY, 
REPLACE(RIGHT(dbo.tblTickets.SUMMARY, CHARINDEX('-', REVERSE(dbo.tblTickets.SUMMARY), 2)), '-', '') AS DiskName
FROM dbo.tblTickets


This view has to be extended with the ServerName. The ServerName is the word between string "ITSM Asset: " and " -"
If a record got next summary:
TARGET: apsnap84 RULE NAME: Database Availability and Critical States MESSAGE: The database status is DOWN. ITSM Asset: JDENAP06 - APSNAP84
The ServerName is JDENAP06.

Remark: the lenght of the ServerName can vary. It is not limited to 8 characters but can be less or more.

Edited by - ino mart on 09/25/2012 04:45:20
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  06:58:37  Show Profile  Reply with Quote
I should have explained better - the code I posted on 09/24/2012 : 10:00:39 was not to meant to imply that you need to create a new table in your database. Since I don't have access to the tables, I simply created a temporary table to insert the test data to demonstrate to you how one would do it. In your real situation, you do not need to create the temp table.

Given the code you posted now, which I assume gives you everything you need, except the servername column, modify the code as shown below. My changes are shown in red.
SELECT dbo.tblTickets.Date,
       dbo.tblTickets.Agent,
       dbo.tblTickets.Severity,
       dbo.tblTickets.SUMMARY,
       REPLACE(
           RIGHT(
               dbo.tblTickets.SUMMARY,
               CHARINDEX('-', REVERSE(dbo.tblTickets.SUMMARY), 2)
           ),
           '-',
           ''
       ) AS DiskName,
       LTRIM(
           RTRIM(
               LEFT(
                   STUFF(dbo.tblTickets.SUMMARY, 1, CHARINDEX('ITSM Asset:', dbo.tblTickets.SUMMARY) + 11, ''),
                   CHARINDEX('-', STUFF(dbo.tblTickets.SUMMARY, 1, CHARINDEX('ITSM Asset:', dbo.tblTickets.SUMMARY) + 11, ''))
                   -1
               )
           )
       ) AS servername
FROM   dbo.tblTickets
Go to Top of Page

vijayan.vinu3
Starting Member

19 Posts

Posted - 09/25/2012 :  08:01:43  Show Profile  Reply with Quote
This query would work for your example.


Declare @string1 Varchar(max) = 'TARGET: apsnap84 RULE NAME: Database Availability and Critical States MESSAGE: The database status is DOWN. ITSM Asset: JDENAP06 - APSNAP84' 
Declare @string2 Varchar(max) = 'TARGET: SHDVIVP1 RULE NAME: Agent unreachable MESSAGE: Failted to connect. ITSM Asset: NCSQAP02 - SHDVIVP1' 
Declare @string3 Varchar(max) = 'TARGET: drcconp1 RULE NAME: Listener Availability MESSAGE: The listener is down ITSM Asset: CPCDWDRP - DRCCONP1'
Select LTRIM(RTRIM(REVERSE(Substring(REVERSE(@string1), CHARINDEX('-', REVERSE(@String1), 1) + 2, CHARINDEX(':', REVERSE(@string1), 1) - CHARINDEX('-', REVERSE(@String1), 1) - 3)))) As ServerName1, 
	   LTRIM(RTRIM(RIGHT(@String1, CharIndex('-', REVERSE(@string1)) - 1))) As DiskName1, 
	   LTRIM(RTRIM(REVERSE(Substring(REVERSE(@string2), CHARINDEX('-', REVERSE(@String2), 1) + 2, CHARINDEX(':', REVERSE(@string2), 1) - CHARINDEX('-', REVERSE(@String2), 1) - 3)))) As ServerName2, 
	   LTRIM(RTRIM(RIGHT(@String2, CharIndex('-', REVERSE(@string2)) - 1))) As DiskName2, 
	   LTRIM(RTRIM(REVERSE(Substring(REVERSE(@string3), CHARINDEX('-', REVERSE(@String3), 1) + 2, CHARINDEX(':', REVERSE(@string3), 1) - CHARINDEX('-', REVERSE(@String3), 1) - 3)))) As ServerName3, 
	   LTRIM(RTRIM(RIGHT(@String3, CharIndex('-', REVERSE(@string3)) - 1))) As DiskName3 


Just replace the temporary variable with the name of the field on which you want to apply the string manipulation.

Hope it helps.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000