Sql Query

[Piedro]

Hello

I don't know if anyone in this group can help me but I've got a very
difficult sql query question.
I've got a table with 2 fields: A field called field and one called
value
it loos like this
FIELD VALUE
X00 0
X01 1000
X02 2000
X10 0
X11 1000
X12 2000
Y00 0
Y01 500
Y10 0
Y11 500

Now out of this table I need the number of records where the values
are equal when the first letter of field equals 'X' or 'Y' and the 2nd
char from the field field differ and the 3th char from the field field
are the same.
So for example:
X01 and X11 because first char X = X , 2nd char differs 0<>1 and 3th
is equal 1 = 1 and the values are equal 1000 = 1000
But not
X01 and Y11 because X <> Y

I hope someone can help with a query for this
thanks a lot
PP

[Stan Sainte-Rose]

Maybe you have to play with the SUBSTRING
Something like

SELECT
Total=(SELECT COUNT(*) from yourtable where
SUBSTRING(FIELD, 1, 1)= SUBSTRING(T.FIELD,1, 1) AND
SUBSTRING(FIELD, 2, 1)<> SUBSTRING(T.FIELD,2, 1) AND
SUBSTRING(FIELD, 3, 1)= SUBSTRING(T.FIELD,3, 1)
)
FROM YourTable T

Stan
"Piedro" <pp*****@hotmail.com> a écrit dans le message de news:
b7**************************@posting.google.com...

Hello

I don't know if anyone in this group can help me but I've got a very
difficult sql query question.
I've got a table with 2 fields: A field called field and one called
value
it loos like this
FIELD VALUE
X00 0
X01 1000
X02 2000
X10 0
X11 1000
X12 2000
Y00 0
Y01 500
Y10 0
Y11 500

Now out of this table I need the number of records where the values
are equal when the first letter of field equals 'X' or 'Y' and the 2nd
char from the field field differ and the 3th char from the field field
are the same.
So for example:
X01 and X11 because first char X = X , 2nd char differs 0<>1 and 3th
is equal 1 = 1 and the values are equal 1000 = 1000
But not
X01 and Y11 because X <> Y

I hope someone can help with a query for this
thanks a lot
PP

[Rob Panosh]

Hope this helps ...

SELECT count(*)
FROM myTable
WHERE substring( field, 1, 1) IN ( 'X', 'Y' ) AND
( substring(field, 2, 1 ) <> substring substring( field, 3,
1 ) OR
substring(field, 2, 1 ) = substring substring(
field, 3, 1 ) )

Regards,
Rob Panosh

"Piedro" <pp*****@hotmail.com> wrote in message
news:b7**************************@posting.google.c om...

Hello

I don't know if anyone in this group can help me but I've got a very
difficult sql query question.
I've got a table with 2 fields: A field called field and one called
value
it loos like this
FIELD VALUE
X00 0
X01 1000
X02 2000
X10 0
X11 1000
X12 2000
Y00 0
Y01 500
Y10 0
Y11 500

Now out of this table I need the number of records where the values
are equal when the first letter of field equals 'X' or 'Y' and the 2nd
char from the field field differ and the 3th char from the field field
are the same.
So for example:
X01 and X11 because first char X = X , 2nd char differs 0<>1 and 3th
is equal 1 = 1 and the values are equal 1000 = 1000
But not
X01 and Y11 because X <> Y

I hope someone can help with a query for this
thanks a lot
PP