Sql Query

  • Thread starter Thread starter Piedro
  • Start date Start date
P

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
 
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
 
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
 
Back
Top