From: johansson.andersson@telia.com   
      
   Hello!   
      
   If I use just this   
   SELECT        SUBSTRING(CId, 9, 4)   
   FROM            cases   
   I get all the number for example   
   1001   
   1002   
   1003   
   ...   
   1130 and so on   
      
   But I want the max value from these. I need to use the substring,cast and   
   max but doesn't know how   
      
   It's something like this   
   SELECT        max(cast(SUBSTRING(CId, 9, 4) AS test) as Int))   
   FROM            cases   
      
   //Tony   
      
    skrev i meddelandet   
   news:3ee6ef1c-1eaf-4fab-b27d-2aadd8c99c1a@googlegroups.com...   
   > On Saturday, 8 December 2012 10:44:31 UTC, Tony Johansson  wrote:   
   >> Hello!   
   >>   
   >> In a field in the database called Cid we have a format like year-35-X   
   >> So in the database we can have numbers that can look like this.   
   >>   
   >>  2012-35-1   
   >> 2012-35-2   
   >> ...   
   >> 2012-35-56   
   >> 2012-35-0145   
   >>   
   >> If I want a select statement that return the largest number for X how can   
   >> I   
   >> write this ?   
   >>   
   >> So if we have 2012-35-0194 in the database field Cid I want to get back   
   >> 195   
   >>   
   >> ?   
   >   
   > It looks as though CAST(SUBSTRING(Cid, 9, 4) AS int) will extract   
   > the number if the data is valid and always that length.   
   >   
   > If the specification is "after the second/last hyphen" with variable   
   > length then something more elaborate is needed, and, again, it   
   > depends on whether the data is reliably of that format; some   
   > simple designs will give either an error or a wrong answer if   
   > the data doesn't conform.   
   >   
   > Also, if you need to read that data, to generate a new key value,   
   > this may be not a very good way to store it.  But sometimes you   
   > don't have the option of using the best design.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)