Using substring in a Group By - 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 2000 Forums

SQL Server Development (2000)

Using substring in a Group By

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-08-10 : 08:10:11

Wasabi writes "Hi, Put on your SQL Caps ;-)

I want to count a number of errors (the field) in a SQL Server 7.0 database. Problem is, all of the errors, error descriptions, error numbers, etc are in one field. I want to parse the data and group by the parsed string, and also count the occurences.

Sample Query:

// The first substring //

Select substring(CAST(strings AS varchar(1000)),PATINDEX('%[A-Z][A-Z][A-Z]-_____%',CAST(strings AS varchar(1000))), 9) As ErrorNumber,

// The replace(substring())

replace(substring (
strings,
CHARINDEX('Host Path:', CAST(strings AS varchar
(1000)))+11, CHARINDEX(' ', CAST(strings AS varchar(1000)), CHARINDEX('Host Path:', CAST(strings AS varchar(1000)))+24) -
CHARINDEX('Host Path:', CAST(strings AS varchar(1000)))-11
),'/queue/','') As HostPath

// The Rest //

From ErrorLogs
where time >= '7/22/01' and time <= '7/28/01' and eventsource
Like 'VBRuntime%'
and strings not like '%Client Error%' And strings Like '%Host
Path:%'
Order By HostPath ASC

Sorry it doesn't look right..

If you're lost.. basically the first substring statement gets an error number like "VBS-00015", my company's custom error numbers.. The second replace(substring()) statement gets the HostPath, basically the file and COM object that produced that error. It gets all of the data, then I would loop through that and count by hand the occurrences.. this takes too long.

One problem is that the errors can have 2 different error numbers for one host path. Another problem is that strings is a text (memo) field. So i have to cast it all the time and I probably lose a couple of seconds doing it. If you know a better way of doing this as well, that would be freakin' excellent.

If you can help great, if you understand what the heck i'm asking, also great.. if not, it is a bit difficult, I understand ;-) Thanks in advance for your help..

Wasabi

Using Windows NT Server 4.0 and SQL server 7 on windows 2000."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources