Filter out data from a string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Filter out data from a string

Author

Topic

ino mart
Starting Member

12 Posts

Posted - 2012-09-24 : 08:07:51

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-24 : 08:27:27

[code]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)))[/code]

ino mart
Starting Member

12 Posts

Posted - 2012-09-24 : 08:50:17

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-24 : 09:04:18

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)))

ino mart
Starting Member

12 Posts

Posted - 2012-09-24 : 09:40:54

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-09-24 : 09:58:33

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-24 : 10:00:39

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

ino mart
Starting Member

12 Posts

Posted - 2012-09-25 : 04:35:55

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:
[CODE]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[/CODE]

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-25 : 06:58:37

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

vijayan.vinu3
Starting Member

19 Posts

Posted - 2012-09-25 : 08:01:43

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources