From: reb01501@NOSPAMyahoo.com   
      
   Tony Johansson wrote:   
   > 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   
   >   
   You're close. You just need to get rid of the alias inside the expression:   
   SELECT        max(cast(SUBSTRING(CId, 9, 4)) as Int)) as maxvalue   
      
   If you're trying to get the next key value, you need to be wary of   
   multi-user activity. Two users running this at the same time will get the   
   same answer. If that's you're goal, let us know and we'll have some   
   solutions for you.   
      
   This could be made more foolproof - it will fail when the X portion exceeds   
   4 digits, right? You can take advantage of a little-known function called   
   parsename(), which accepts a string containing up to 4 portions separated by   
   periods. It's intended to be used to parse object names in   
   server.database.schema.object format, but you can use it for strings with   
   other delimiters by using the replace() function to replace the delimiters   
   with periods. In your case it would look like this:   
      
   SELECT MAX(CAST(PARSENAME(REPLACE(CId,'-','.'),1) AS INT)) AS maxvalue   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)