Help with SUBSTRING and CHARINDEX

  • I have the following in a column by the name of Text in a table:

    Login failed for user 'User1'. Reason: Failed to open the explicitly specified database. [CLIENT: 11.11.11.11]

    And, I would like to pull out User1 and 11.11.11.11 from the string. For some reason I have always struggled with this type of thing in TSQL. Any and all help with this will be greatly appreciated.

    Thank You

  • Maybe something like this?

    CREATE TABLE #Test(

    ErrorString varchar(400)

    );

    INSERT INTO #Test VALUES('Login failed for user ''User1''. Reason: Failed to open the explicitly specified database. [CLIENT: 11.11.11.11]');

    SELECT *,

    SUBSTRING( ErrorString, 24, CHARINDEX( '''', ErrorString, 25) - 24) AS Username,

    SUBSTRING( ErrorString, CHARINDEX('[CLIENT:', ErrorString) + 9, LEN(ErrorString) - CHARINDEX('[CLIENT:', ErrorString) - 9) AS Client

    FROM #Test

    WHERE ErrorString LIKE 'Login failed for user%'

    GO

    DROP TABLE #Test

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Quick suggestion, similar to Luis's

    😎

    DECLARE @LOG_STRING VARCHAR(200) = 'Login failed for user ''User1''. Reason: Failed to open the explicitly specified database. [CLIENT: 11.11.11.11]';

    SELECT

    SUBSTRING

    ( @LOG_STRING

    ,CHARINDEX(CHAR(39),@LOG_STRING,1) + 1

    ,CHARINDEX(CHAR(39),@LOG_STRING,CHARINDEX(CHAR(39),@LOG_STRING,1) + 1) - (CHARINDEX(CHAR(39),@LOG_STRING,1) + 1)

    ) AS STR_USER

    ,SUBSTRING

    ( @LOG_STRING

    ,CHARINDEX('[CLIENT: ',@LOG_STRING,1) + 9

    ,CHARINDEX(CHAR(93),@LOG_STRING,CHARINDEX(CHAR(91),@LOG_STRING,1)) - (CHARINDEX('[CLIENT: ',@LOG_STRING,1) + 9)

    ) AS STR_CLIENT

    ;

    Output

    STR_USER STR_CLIENT

    --------- ------------

    User1 11.11.11.11

  • Thank you both very much, both of these will work for me!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply