How to extract characters of a String and do MAX?

• lopes80andre

  SSC Veteran

  Points: 249

  More actions

  August 5, 2009 at 11:14 am

  #156064

  Hi,

  First of all sorry for my english.

  I need to extract some chars of a string.

  I have this data as ID_KEY:

  AC/01/2008

  AC/02/2008

  AC/03/2008

  AC/04/2008

  AC/05/2008

  AC/06/2008

  AC/07/2008

  AC/08/2008

  BC/01/2008

  ....

  I need to cut this string in 3 variables, for example: (var1='AC', var2= '01', var3='2008')

  Next I need to do a Select to find the MAX value of the 'AC' '2008'... tha MAX of 'AC' '2008' is '08'

  How can I deal with this? How to extract the value '08' of the 'AC' '2008'? Some ideas?

  Best Regards,

  André Lopes.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  August 5, 2009 at 11:21 am

  #1034978

  Try this, see if it'll do what you need:

  create table #T (

  ID int identity primary key,

  Col1 char(10));

  insert into #T (Col1)

  select 'AC/01/2008' union all

  select 'AC/02/2008' union all

  select 'AC/03/2008' union all

  select 'AC/04/2008' union all

  select 'AC/05/2008' union all

  select 'AC/06/2008' union all

  select 'AC/07/2008' union all

  select 'AC/08/2008' union all

  select 'BC/01/2008';

  select substring(Col1, 1, 2), max(substring(Col1, 4, 2)), substring(Col1, 7, 4)

  from #T

  group by substring(Col1, 1, 2), substring(Col1, 7, 4);

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon