ogeddie


I'm trying to find the best way to return distinct data from a column (Message). The problem is that the messages are not distinct. Ex.

Message1 - An unhandled exception occurred type 123456
Message2 - An unhandled exception occurred type 346742

Both of these messages are being returned since they are not unique. Now I was thinking about doing a character length and dropping the message after x number of chars, however, there are other issues like the unique data in the message isn't always in the same location. Any ideas of how to query this would be much appreciated.



Re: Trying to return DISTINCT COLUMN data with varying information

Rob Farley


You can easily do a distinct based on a substring... for example, if you wanted to do something like:

select distinct substring(Message, 10, 20) as yoursubstring
from yourTable

--then that might do what you're after. But I'm really not sure what you're trying to achieve here. Can you give some more examples about what you're aiming for

Rob






Re: Trying to return DISTINCT COLUMN data with varying information

ogeddie

Thanks Rob for the feeback. Basically the data I get back from my query, which I'm trying to return as distinct is returning non distinct values. The problem that I'm facing is as follows.

Returned Data (from column 'Message')-

-An unhandled exception occurred type JOE
-An unhandled exception occurred type KEN

I'm trying to only return distinct values but both of the above messages are returning because they contain different values in the message. Also, in other messages that are being returned, the variable data (ex. type JOE) is not always located in the same place.

Ex.

- An unhandled exception type JOE occurred
or
- type JOE An unhandled exception occurred.

I need to be able to somehow filter the data so that they are returning as one distinct message as oppose to having 10,000 returns due to a varying piece of data.

Things that can possibly help with the query. The word "type" always precedes the variable that changes. The variable that changes always contains same character length.





Re: Trying to return DISTINCT COLUMN data with varying information

Rob Farley

...But 10,000 is an exaggeration right

You could use patindex to work out where the word 'type' is, and then get the string from there (plus five characters) to the next non-numeric character.

You may want to do the 'distinct' before you do all your string processing though - run some tests to see which is quicker.

Rob




Re: Trying to return DISTINCT COLUMN data with varying information

Louis Davidson

This to me sounds like a job for the people that put out those error messages. I mean, this is a lot of unnecessary work to decide that these two values:

- An unhandled exception type JOE occurred
or
- type JOE An unhandled exception occurred.

Are in fact the same value. You could do it with some well considered replace statements possibly, if you know the valid domain of possible error messages:

declare @value1 varchar(100)
declare @value2 varchar(100)

set @value1 = 'An unhandled exception type JOE occurred'
set @value2 = 'type JOE An unhandled exception occurred.'

set @value1 =rtrim(ltrim(replace(replace(replace(replace(replace(replace(@value1,'.',''),'unhandled',''),'An',''),'exception',''),'type',''),'occurred','')))
set @value2 =rtrim(ltrim(replace(replace(replace(replace(replace(replace(@value2,'.',''),'unhandled',''),'An',''),'exception',''),'type',''),'occurred','')))

select case when @value1 = @value2 then 'Match' else 'Mismatch' end

Hopefully I have missed your point by a mile :)






Re: Trying to return DISTINCT COLUMN data with varying information

ogeddie

Thanks for you replies.. i will try both styles and see if I can get the desired outcome. And no Rob, I wasn't exaggerating about the 10,000 returns :(.




Re: Trying to return DISTINCT COLUMN data with varying information

Rob Farley

Wow... 10,000 rows that can be shrunk into a single one with a little string manipulation. I'm still somewhat dubious, but either way...

A while back I wrote a function to return the numbers from a string. It used an auxiliary table of numbers, but it could still work okay for you. I think you'd be better off looking for the word 'type' though and then jumping in from there.

Rob





Re: Trying to return DISTINCT COLUMN data with varying information

ogeddie

Just out of curiosity, do you mind if I see your function you wrote