Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: Solved

    Hi thanks for your help
    Last edited by melvill; 05-09-12 at 07:26.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    CREATE TABLE #AlertDescription(	messageStr	VARCHAR(255))
    
    INSERT INTO #AlertDescription(messageStr) VALUES
    (' gqhg has qhf gsfghsdfh gg hgjh WARNING! XYZ Job has taken longer than it should ghd xcwn,vn,j WARNING! nv,;vcnv,;'),
    (' gqhg has qhf gsfghsdfh gg hgjh WARNING! ABCD Job has taken longer than it should ghd xcwn,vn,j WARNING! nv,;vcnv,;'),
    (' gqhg has qhf gsfghsdfh gg hgjh WARNING! QWERT Job has taken longer than it should ghd xcwn,vn,j WARNING! nv,;vcnv,;')
    
    SELECT messageStr, 
    	CHARINDEX('WARNING! ', messageStr) as locationWARNING, 
    	CHARINDEX(' has ', messageStr, CHARINDEX('WARNING! ', messageStr) + LEN('WARNING! ') + 1) as LocationHAS, 
    	substring(messageStr, CHARINDEX('WARNING! ', messageStr) + LEN('WARNING! ') + 1, 
    	CHARINDEX(' has ', messageStr, CHARINDEX('WARNING! ', messageStr) + LEN('WARNING! ') + 1)- 
    	(CHARINDEX('WARNING! ', messageStr) + LEN('WARNING! ') + 1)) MySubString
    FROM #AlertDescription
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    May 2012
    Posts
    3
    Hey there

    Unfortunately i dont have access to create tables and the table already exists with the job names. Any more ideas?

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I don't have access to you table, so I created a temporary table to show you what code you need to make it work.

    In
    Code:
    select TBL2.DisplayName as 'Instance',
    TBL1.AlertName,
    TBL1.AlertDescription as 'Job Name' ,
    ...
    Replace TBL1.AlertDescription by this code
    Code:
    substring(TBL1.AlertDescription, CHARINDEX('WARNING! ', TBL1.AlertDescription) + LEN('WARNING! ') + 1, 
    	CHARINDEX(' has ', TBL1.AlertDescription, CHARINDEX('WARNING! ', TBL1.AlertDescription) + LEN('WARNING! ') + 1)- 
    	(CHARINDEX('WARNING! ', TBL1.AlertDescription) + LEN('WARNING! ') + 1)) as 'Job Name' ,
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    May 2012
    Posts
    3
    Thank you so much, that worked almost perfectly. Changed it a little and its perfect.\

    Thanks again for your time. You are a legend

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •