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

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

Re: How do I write this sql statement

Show all headers | 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