Formatting "2011-2012 Q1" as "Q1 11-12" in SQL Server 2005

Basically my table contains a fiscal year field that looks like "20开发者_高级运维11-2012 Q1" and I want to output that as "Q1 11-12" using SQL only if possible. This is for an ASP.NET page if that helps.

how about;

;with test (fiscal) as (
    select  '2011-2012 Q1' union
    select  '1990-1991 Q2' union
    select  '2012-2013 Q4' union
    select  '1999-1999 Q3'  
)
select
    right(fiscal, 2) + ' '
    +
    substring(fiscal, 3, 2) + '-'
    +
    substring(fiscal, 8, 2)
from test


>>Q2 90-91
>>Q3 99-99
>>Q1 11-12
>>Q4 12-13

declare @s varchar(20)
set @s = '2011-2012 Q1'
select right(@s, 2) + ' ' + substring(@s, 3, 3) + substring(@s, 8, 2)

There must be a better way but you could use this

PRINT RIGHT('2011-2012 Q1', 2) + ' ' + RIGHT(LEFT('2011-2012 Q1', 4), 2) + '-' + RIGHT(LEFT('2011-2012 Q1', 9), 2)

which returns

Q1 11-12

Please note that SQL Server is not really designed for string manipulation and I would encourage you to put the display logic where it really belongs - in you UI

SELECT RIGHT(col, 2) +' '+ SUBSTRING(STUFF(col, 6,2,''), 3,5)
FROM (SELECT '2011-2012 Q1' col) t