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

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

Re: How do I write this sql statement

Show all headers | View raw

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

<rja.carnegie@gmail.com> 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. 

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