none
Using Substring and CharIndex

    Question

  • I have a column on a table in a SCOM datawarehouse DB that contains a block of information. i.e

    The process Explorer.EXE has initiated the shutdown of computer SERVER1 on behalf of user domain\john smith for the following reason: Other (Planned)

    From my query I am trying to get just the domain\username returned.  Is this possible?

    I've been playing around with substring and charindex, which I have got to remove all the date to the left of the server name, but can't see how I can add another substring to the same query to remove everything to the right?

    select
    SUBSTRING(RTRIM(ALERTDESCRIPTION),charindex('on behalf of user',ALERTDESCRIPTION)+18, len(ALERTDESCRIPTION))
    FROM
    [Alert].[vAlert]

    Is this the right approach?

     
    Friday, August 23, 2013 3:40 PM

Answers

  • declare @s varchar(1500)='The process Explorer.EXE has initiated the shutdown of computer SERVER1 on behalf of user domain\john smith for the following reason: Other (Planned)'
     
    declare @test table(id int identity(1,1), ALERTDESCRIPTION varchar(1500) );
    insert into @test values(@s);
      
    SELECT
    Replace(SUBSTRING(ALERTDESCRIPTION, charindex('on behalf of user',ALERTDESCRIPTION), charindex('for the following reason:',ALERTDESCRIPTION)-charindex('on behalf of user',ALERTDESCRIPTION)),'on behalf of user','')
    as domainAndUser
    FROM @test

    Friday, August 23, 2013 6:32 PM
    Moderator