Post reply

Using LEFT() and Right() function to extract data

  • February 4, 2019 at 1:18 pm

    #379285

    How can I combine right and left string function to extract data for one column
    I am trying to extract just the IP address.

    Column Name: Text
    Data Type: nvarchar(max)

    Data sample:

    Login failed for user 'WebLogic_VACHOICE_TW_Prd'. Reason: Failed to open the explicitly specified database 'PCM_VAChoice_TW'. [CLIENT: 10.219.69.46]

    SELECT
    --RIGHT(TEXT,CHARINDEX(':',REVERSE(TEXT))-1)
    --LEFT(TEXT, CHARINDEX(']',TEXT)-1)
    FROM [tempdb].[dbo].[ErrorLogInfo]
    where Processinfo = 'Logon'  and Text Like '%Login Failed for user%'

    --RIGHT(TEXT,CHARINDEX(':',REVERSE(TEXT))-1)

    will produce the following result:

    10.219.69.46]

    --LEFT(TEXT, CHARINDEX(']',TEXT)-1)

    will produce the following result:

    Login failed for user 'WebLogic_VACHOICE_TW_Prd'. Reason: Failed to open the explicitly specified database 'PCM_VAChoice_TW'. [CLIENT: 10.219.69.46

    The  desired result is:

    10.219.69.46

  • February 4, 2019 at 1:42 pm

    #2021088

    Is the message always of the form  'message [CLIENT: nnn.nnn.nnn.nnn]' ?
    If so this will do it:
    DECLARE @SearchStringForStart varchar(100)='CLIENT: '
    DECLARE @LenSearchStringForStart int = LEN(@SearchStringForStart)
    DECLARE @SearchStringForEnd varchar(100)=']'
    DECLARE @LenSearchStringForEnd int = LEN(@SearchStringForEnd)

    SELECT SUBSTRING(T.C,StartOfIP,V.EndOfIP-U.StartOfIP)
    FROM (SELECT NULL X) X
    CROSS APPLY(VALUES ('Login failed for user ''WebLogic_VACHOICE_TW_Prd''. Reason: Failed to open the explicitly specified database ''PCM_VAChoice_TW''. [CLIENT: 10.219.69.46]')) T(C)
    CROSS APPLY(VALUES (CHARINDEX(@SearchStringForStart ,T.C)+@LenSearchStringForStart+1)) U(StartOfIP)
    CROSS APPLY(VALUES (CHARINDEX(@SearchStringForEnd,T.C,U.StartOfIP))) V(EndOfIP)

Viewing 2 posts - 1 through 2 (of 2 total)

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