Re: Select Cast Error

On fim, 2006-09-21 at 21:57 -0400, Brad Budge wrote:
> I have a field that is varchar(15) type and an example of data I'm
> working with is (PROJ-0001-06)

> select cast((max(substring(test.test from 6 for 4))) as integer) + 1
> FROM test where max(substring(test.test from 11 for 2));
>

as someone already pointed out, this where clause
is just equivalent to WHERE '06'
and does not mean anything


> List below is a better idea of what my table looks like and the result
> I need.
> PROJ-0004-05
> PROJ-0001-06
> PROJ-0002-06
> PROJ-0003-06

> When I run my select statement I want to return the number 4.  The
> idea is that I need the next highest number in the middle but take in
> consideration that the highest trailing numbers take president.

if all the values are really formatted like this, you could ORDER BY to
get the value you want:

SELECT
   1 +
   CAST(substring(test.test from 6 for 4) AS INTEGER)
FROM test
ORDER BY
   substring(test.test from 11 for 2) DESC,
   substring(test.test from 6 for 4) DESC
LIMIT 1;

gnari