Post reply

Comparing strings

  • May 31, 2011 at 4:06 pm

    #389626

    Hello

    I was wondering if anyone could help me. I'm new to T-SQL so forgive me if this is a simpleton question

    I need to compare 2 strings

    I have 2 tables, one contains the full string and the second, the search string.

    Full String:

    'The process started at 16:03:14 failed to create System.PropertyBagData. Errors found in output:

    D:\Program Files\System Center Operations Manager 2007\Health Service State\Monitoring Host Temporary Files 210\654\GetOpsMgrDBPercentFreeSpace.vbs(42, 2) Microsoft OLE DB Provider for SQL Server: Login failed for user ''. The user is not associated with a trusted SQL Server connection.'

    my lookup table looks like this:

    'NetbiosName' 'MappingType' 'SearchText' 'Discard'

    'SERVER1' 'DESCRIPTION' 'failed to create System.PropertyBagData' 'Y'

    Basically what I'm trying to do is search the lookup table, if the text matches the conditions SERVER1=SERVER1, MappingType=Description, SearchText is contained in the full string

    What i want it to return is the Discard value Y or N

    if this doesn't make sense, let me know. sorry T-SQL is not my strong point but I'm learning.If I've thought about this logically incorrect, let me know

    thanks

  • May 31, 2011 at 4:37 pm

    #1332302

    DECLARE @StringtoCompare varchar(MAX)

    DECLARE @FullString varchar(max)

    SET @StringtoCompare =

    (SELECT Text FROM SCOM_MONITOR_MAP

    WHERE NetBiosName = 'SERVER1')

    SET @Fullstring= 'The process started at 16:03:14 failed to create System.PropertyBagData. Errors found in output'

    PRINT @StringtoCompare

    PRINT @FullString

    something along these lines....how do i compare the 2 strings to return me value of the discard column

    i've prob got this all wrong :ermm:

  • May 31, 2011 at 7:24 pm

    #1332329

    My recommendation would be to lookup the "LIKE" operator in Books Online.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 1, 2011 at 9:08 am

    #1332550

    erm thanks.......

    Bit if sleep helped and figured it outmyself. I've left this here incase anyone needs stumbles on the thread in the future.

    USE Data

    GO

    DECLARE @SearchString varchar(MAX)

    DECLARE @SearchCount int

    DECLARE mapper_cursor CURSOR FOR

    SELECT Text FROM SCOM_MONITOR_MAP WHERE NetBiosName = 'SERVER1' AND MappingType = 'DESCRIPTION'

    ORDER BY UniqueID;

    OPEN mapper_cursor;

    FETCH NEXT FROM mapper_cursor

    INTO @SearchString;

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

    WHILE @@FETCH_STATUS =0

    BEGIN

    -- Do Stuff Here

    IF

    (SELECT COUNT (*) from SCOM_ACTION_QUEUE WHERE Description LIKE + '%' + @SearchString + '%' AND AlertID = 'd0991daf-f70b-4499-bab7-71a067d735a5') > 0

    SELECT Discard from SCOM_MONITOR_MAP WHERE Text = @SearchString

    -- This is executed as long as the previous fetch succeeds.

    FETCH NEXT FROM mapper_cursor

    INTO @SearchString;

    END

    CLOSE mapper_cursor;

    DEALLOCATE mapper_cursor;

    GO

  • June 1, 2011 at 9:12 am

    #1332556

    Congrats, now learn to do it right!

    read up on inner join and remove that cursor (worst possible way to code this in sql server)

  • June 1, 2011 at 10:19 am

    #1332589

    can you elaborate?

    what wrong with using cursor?

  • June 1, 2011 at 10:24 am

    #1332592

    Sayeed Master (6/1/2011)

    can you elaborate?

    what wrong with using cursor?

    Slow as hell in sql server.

    It's faster to use an inner join and filter everything there.

  • June 1, 2011 at 10:40 am

    #1332607

    ok no worries. I'll read up on it.

    thanks for the pointer

  • June 2, 2011 at 3:36 am

    #1332976

    Yup you're right. Thanks for the hint about the inner join, thats alot cleaner method. Apologies but I'm new to SQL, still learning.

    Just a means to end.

  • June 2, 2011 at 5:45 am

    #1333024

    Sayeed Master (6/2/2011)

    Yup you're right. Thanks for the hint about the inner join, thats alot cleaner method. Apologies but I'm new to SQL, still learning.

    Just a means to end.

    We've all been there! 🙂

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

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