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

Re: How do I write this sql statement

Path	csiph.com!usenet.pasdenom.info!gegeweb.org!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail
From	"Tony Johansson" <johansson.andersson@telia.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: How do I write this sql statement
Date	Sat, 8 Dec 2012 14:31:19 +0100
Organization	A noiseless patient Spider
Lines	57
Message-ID	<k9vffh$pv9$1@dont-email.me> (permalink)
References	<k9v5ml$7ln$1@dont-email.me> <3ee6ef1c-1eaf-4fab-b27d-2aadd8c99c1a@googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding	7bit
Injection-Date	Sat, 8 Dec 2012 13:31:29 +0000 (UTC)
Injection-Info	mx04.eternal-september.org; posting-host="da50b8a0e4970e2b9711eebf9392fd1d"; logging-data="26601"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+v8/JKsVOJe5tvrwrsen5s"
X-MimeOLE	Produced By Microsoft MimeOLE V6.0.6002.18463
In-Reply-To	<3ee6ef1c-1eaf-4fab-b27d-2aadd8c99c1a@googlegroups.com>
X-Newsreader	Microsoft Windows Mail 6.0.6002.18197
Cancel-Lock	sha1:kin4PhMs8sTbCFhB4H4uwGQfskI=
X-Priority	3
X-MSMail-Priority	Normal
Xref	csiph.com comp.databases.ms-sqlserver:1364

Show key headers only | 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