Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Apr 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question syntax SQLserver2005

    I need to compare two fields
    victimOF (varchar(20), not null) AND chrgSeq (int, not null)

    I didn't create the database; I'm trying to pull report(s). So, I have to work with the fields as they are. Here's a table of values the fields contain.

    victimOF chrgSeq
    -------- --------
    1,2,4 2 chrgSeq(2) is contained in victimOF(1,2,4)
    2,3,4 3 chrgSeq(3) is contained in victimOF(2,3,4)
    2,3 1 chrgSeq(1) is NOT contained in victimOF(2,3)

    I think I need my WHERE clause to contain
    WHERE ... victimOF LIKE '%{chrgSeq}%'

    I've been wrestling CONVERT/CAST for hours to convert the int field to some text-string value I can work with. I've been trying to concatenate percent-sign, chrgSeq-convertedToSTR, percent-sign. PLEASE HELP !

  • #2
    New to the CF scene
    Join Date
    Apr 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    forum removed my multipe spaces that I'd added in to align the text..

    I need to compare two fields
    victimOF (varchar(20), not null) AND chrgSeq (int, not null)

    I didn't create the database; I'm trying to pull report(s). So, I have to work with the fields as they are. Here's a table of values the fields contain.

    victimOF
    --------
    1,2,4
    2,3

    chrgSeq
    --------
    2
    3

    chrgSeq(value 2) is contained in victimOF(value--1,2,4), in victimOF(value--2,3)
    chrgSeq(value 3) is contained in victimOF(value--2,3)

    I think I need my WHERE clause to contain
    WHERE ... victimOF LIKE '%{chrgSeq}%'

    I've been wrestling CONVERT/CAST for hours to convert the int field to some text-string value I can work with. I've been trying to concatenate percent-sign, chrgSeq-convertedToSTR, percent-sign. PLEASE HELP !

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    If I give you a rotten tomato, will you throw it in the face of the total idiot who designed that database?

    Yes, you can do it. It's sneaky, but possible.

    The problem you have is avoiding "false positives".

    For example, say that the victimOf field contains "23,37" and the chrgSeq field is "3". If you simply do
    Code:
        WHERE victimOf LIKE '%3%'
    (don't worry yet about how to do that), you can see that you would get a "false positive" on both the "23" and the "37".

    So the (very very ugly!) answer is to transform that to this:
    Code:
        WHERE ',23,37,' LIKE '%,3,%'
    And now you don't get any false positives but you will get a correct positive for (examples) either
    Code:
        WHERE ',3,37,' LIKE '%,3,%'
    or
        WHERE ',23,37,' LIKE '%,23,%'
    Onward:

    Code:
    SELECT * FROM table 
    WHERE ',' + victimOf + ',' LIKE '%,' + CONVERT(VARCHAR(20),chrgSeq) + ',%'
    Makes sense?

    How about a rotten tomato and a rotten egg?

    Oh...and the only way to preserve spaces in this (and in most) forums is to wrap your text in [ code ] ... [ /code ] tags (even if it's not really code...and of course no spaces in the actual tags).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •