Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1365

Re: How do I write this sql statement

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

How do I write this sql statement "Tony Johansson" <johansson.andersson@telia.com> - 2012-12-08 11:44 +0100
  Re: How do I write this sql statement rja.carnegie@gmail.com - 2012-12-08 03:37 -0800
    Re: How do I write this sql statement "Tony Johansson" <johansson.andersson@telia.com> - 2012-12-08 14:31 +0100
      Re: How do I write this sql statement "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-12-08 09:15 -0500
  Re: How do I write this sql statement Erland Sommarskog <esquel@sommarskog.se> - 2012-12-08 16:50 +0100
    Re: How do I write this sql statement bill <billmaclean1@gmail.com> - 2013-01-28 22:43 -0800

csiph-web