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

Re: How do I write this sql statement

Received	by 10.224.184.11 with SMTP id ci11mr6607793qab.1.1354966669121; Sat, 08 Dec 2012 03:37:49 -0800 (PST)
Received	by 10.49.75.195 with SMTP id e3mr2002630qew.24.1354966669098; Sat, 08 Dec 2012 03:37:49 -0800 (PST)
Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!c8no2007320qao.0!news-out.google.com!gf5ni48613681qab.0!nntp.google.com!c8no2045856qao.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Sat, 8 Dec 2012 03:37:48 -0800 (PST)
In-Reply-To	<k9v5ml$7ln$1@dont-email.me>
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=94.196.153.101; posting-account=dELd-gkAAABehNzDMBP4sfQElk2tFztP
NNTP-Posting-Host	94.196.153.101
References	<k9v5ml$7ln$1@dont-email.me>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<3ee6ef1c-1eaf-4fab-b27d-2aadd8c99c1a@googlegroups.com> (permalink)
Subject	Re: How do I write this sql statement
From	rja.carnegie@gmail.com
Injection-Date	Sat, 08 Dec 2012 11:37:49 +0000
Content-Type	text/plain; charset=ISO-8859-1
Xref	csiph.com comp.databases.ms-sqlserver:1363

Show key headers only | View raw

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