syntax SQLserver2005

[cynthiakelly]

:( 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 !

[cynthiakelly]

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 !

[Old Pedant]

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
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:

WHERE ',23,37,' LIKE '%,3,%'

And now you don't get any false positives but you will get a correct positive for (examples) either

WHERE ',3,37,' LIKE '%,3,%'
or
WHERE ',23,37,' LIKE '%,23,%'


Onward:


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).