Need to search the eventid in the column and need to give a appropriate name

i didn't quite understand your requirement.
you want to loop through the information in this column and group all the entries based on keyword event id.

for instance, all the entries in the column which has the word "database" in it, group them under proper name and return it back.

is that what u mean?

do u have mapping table which maps the keyqord to look and the proper name you with to apply?

for example:
Table: MappingNames
Keyword | Title
-----------------------------
database | DB Issues
network | networking Issues

and so on.

No we don't have mapping table.

If the eventid Event1479 then it's mirroring issue.
Keyword will be given by us

Like from tale if i get the o/p like
column            count
eventid 1479     2
evebtid 3041   10

the final output will be

column                count           ---------------mirroring&backup.. so on will be given by us.
mirroring issue     2
backe up failed    10

I will analysis the eventid's and will give own caption for that .

can we assume that "Event+number" keyword exists only once in each description in ticketdesc table?

cause i was thinking to use regex to find the event and id and use it to group them.

Yes it will occur only once in the ticket

Hello Experts,
Can anyone look into this question?

@admin:I don't see any improvement in this question

i am getting the result as below

Event 1200      3
Event 1204      2
Event 1216      6
Event 17063      29
Event 18204      15
Event 208      18
Event 3041      24
Event 9002      3

now how can i give the substitute name instead of event id's ?

do u have a table which maps event id to issue name or something like that?

nope, I have to give them a name in a hard code. If none of the code matches then it should go to unknown caption.

so why not create another table in which u map the event id to issue name or description?
and in your sql query get the issue name via the event id.

No this production Database, I won't be able to modify any chances :(

Okay , is it possible to get issues instead of Eventid?

where are the issues names are located?

It's present in the ticketdesc table

eg : ShortDesc: Event1479.MSSQLServer.SQLServer.Themirroringconneconh

You can see the attachment.

Insteadof getting eventid which you posted on ID: 37754502

It could be better if get "MSSQLServer.SQLServer.Themirroringconneconh..."

and group them by title name

can u post the sql query here?
i'll make the change on top of that.

;WITH tickets(Ticketdesc) AS (
    SELECT 'ShortDesc: Event1479.MSSQLServer.SQLServer.Themirroringconneconh...' UNION ALL
    SELECT 'Event1454.MSSQLSERVER.Applicaon..MSSQLSERVER...' UNION ALL
    SELECT 'Event1454.MSSQLSERVER.Applicaon.SQLServer2K8.DBMPAGEU...' UNION ALL
    SELECT 'Event17063.MSSQLSERVER.Applicaon..17063Gener...' UNION ALL
    SELECT 'Control error.'
)
SELECT EventID=STUFF(LEFT(Eventdesc, PATINDEX('%[.]%', Eventdesc)-1), 6, 0, ' ')
     , CountOfEvents=COUNT(*)
FROM (
    SELECT Eventdesc=SUBSTRING(Ticketdesc, PATINDEX('%Event[0-9]%', Ticketdesc), 15)
    FROM tickets
    WHERE PATINDEX('%Event[0-9]%', Ticketdesc) > 0
) derived
GROUP BY LEFT(Eventdesc, PATINDEX('%[.]%', Eventdesc)-1)
;

Kindly Look into this problem