Help with SUBSTRING and CHARINDEX

Question

Post reply

Help with SUBSTRING and CHARINDEX

GBeezy

SSCarpal Tunnel

Points: 4723
More actions
March 4, 2016 at 12:39 pm

#316688

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

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 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

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 3

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