none
Getting ;00000000; out of an error description RRS feed

  • Question

  • Hi,

    I work with an error log table that includes an column called error_description and it will give a description that are around 600 chars. I want my select statement only to shot parts of the error description. The unique part of the error description is always this: ;20160203;(8 numbers with semicolon on each side).  it's surrounded by two ID's like this: ;1234;20160203;12345;, and these two ID's is what i want my statement to show. So i guess i need to locate 8 numbers within two semicolons, and then somehow take out all the numbers from the left semicolon and to the next left semicolon, and the same for the right side ID. I don't know if you understand what i mean or if it's even possible. But this would have saved me a lot of work :)


    • Edited by AndersAu Wednesday, August 3, 2016 11:49 AM
    Wednesday, August 3, 2016 11:47 AM

Answers

  • Try this:

    DECLARE @table TABLE (msg varchar(600));
    
    INSERT INTO @table (msg)
        SELECT 'afdasdfdsagawerwe;1234;20160203;12345;132094fadadsf023942adsfdadsf,afsd9843r543;jds'
        UNION ALL
        SELECT 'afdas23;1234;20161223;12345;132,a;jds'
    
    
    SELECT SUBSTRING(msg, PATINDEX('%;[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9];%', msg) + 1, 8)
    FROM @table
    
    

    Wednesday, August 3, 2016 12:16 PM
    Moderator

All replies

  • Try this:

    DECLARE @table TABLE (msg varchar(600));
    
    INSERT INTO @table (msg)
        SELECT 'afdasdfdsagawerwe;1234;20160203;12345;132094fadadsf023942adsfdadsf,afsd9843r543;jds'
        UNION ALL
        SELECT 'afdas23;1234;20161223;12345;132,a;jds'
    
    
    SELECT SUBSTRING(msg, PATINDEX('%;[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9];%', msg) + 1, 8)
    FROM @table
    
    

    Wednesday, August 3, 2016 12:16 PM
    Moderator
  • Hi, you need to use regular expressions along with Substring and CharIndex T-SQL functions.

    http://regexr.com/
    Wednesday, August 3, 2016 12:21 PM