none
How to order my periods RRS feed

  • Question

  • see my code

    create table #tbl1 
    (id int identity(1,1),
    fld1 varchar(50),
    )
    
    INSERT INTO #tbl1 VALUES
    ('1Q 2014'),
    ('2Q 2014'),
    ('2012 FY'),
    ('1Q 2013'),
    ('2Q 2013'),
    ('4Q 2014'),
    ('4Q 2013'),
    ('2013 FY'),
    ('2011FY'),
    ('3Q 2014'),
    ('3Q 2013'),
    ('2010 FY'),
    ('2014 FY'),
    ('1H 2013'),
    ('2H 2013'),
    ('1H 2014'),
    ('2H 2014')
    
    select id,fld1,substring(fld2,4,4),substring(fld2,1,2) from (
    select *,
    case when fld1 not like '%Q%' then 'AQ '+ fld1
    when fld1 not like '%H%' then 'AQ '+ fld1
    else fld1
    end [fld2]
    from #tbl1
    )t
    order by substring(fld2,4,4),substring(fld2,1,2)
    
    drop table #tbl1

    1H 2014 will come after 2Q 2014 and 2H 2014 will come after 4Q 2014

    so 1H will come after 2Q and 2H will come after 4Q

    tell me how to restructure my above code where i tried but could not do it. thanks

    Saturday, May 23, 2020 10:41 AM

Answers

  • Not really a trivial problem, but I think this one cuts it:

    ; WITH pickapart AS (
       SELECT fld1,
              year = CASE WHEN fld1 LIKE '[0-9][0-9[0-9}[0-9]%' THEN left(fld1, 4)
                          WHEN fld1 LIKE '%[0-9][0-9[0-9}[0-9]' THEN right(fld1, 4)
                     END,
              rest = trim(CASE WHEN fld1 LIKE '[0-9][0-9[0-9}[0-9]%' THEN right(fld1, len(fld1) - 4)
                               WHEN fld1 LIKE '%[0-9][0-9[0-9}[0-9]' THEN left(fld1, len(fld1) -  4)
                          END)
       FROM  #tbl1
    ), moreparts AS (
       SELECT fld1, year,
              sorter = CASE WHEN rest = 'FY'        THEN '4C'
                            WHEN rest LIKE '[0-9]Q' THEN left(rest, 1) + 'A'
                            WHEN rest LIKE '[0-9]H' THEN convert(char(1), 2*convert(int, left(rest, 1))) + 'B'
                       END
       FROM   pickapart
    )
    SELECT year, fld1, sorter
    FROM   moreparts
    ORDER  BY year, sorter
    The trim function was added in SQL 2017. On older version use rtrim and ltrim instead.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se


    Saturday, May 23, 2020 11:37 AM
  • select id,  fld1  
    from #tbl1 
    Order by Cast(Ltrim(rtrim(replace(replace(replace(replace(replace(replace(replace(fld1,'1Q',''),'2Q',''),'3Q',''),'4Q','')
    ,'FY',''),'1H',''),'2H','')))
    +'-'+
    CASE WHEN Left(fld1,2) = '1Q' THEN '01'
    WHEN Left(fld1,2) = '2Q' THEN '04'
    WHEN Left(fld1,2) = '3Q' THEN '07'
    WHEN Left(fld1,2) = '4Q' THEN '10' 
    WHEN Left(fld1,2) = '1H' THEN '06' 
    WHEN Left(fld1,2) = '2H' THEN '12' 
    WHEN Right(fld1,2)= 'FY' THEN '12' 
    Else NULL END +  
    Case   WHEN Right(fld1,2) = 'FY' THEN '-31'
    else  '-01' end  as DATE)

    • Marked as answer by Sudip_inn Tuesday, May 26, 2020 6:14 AM
    Saturday, May 23, 2020 7:06 PM
    Moderator

All replies

  • Not really a trivial problem, but I think this one cuts it:

    ; WITH pickapart AS (
       SELECT fld1,
              year = CASE WHEN fld1 LIKE '[0-9][0-9[0-9}[0-9]%' THEN left(fld1, 4)
                          WHEN fld1 LIKE '%[0-9][0-9[0-9}[0-9]' THEN right(fld1, 4)
                     END,
              rest = trim(CASE WHEN fld1 LIKE '[0-9][0-9[0-9}[0-9]%' THEN right(fld1, len(fld1) - 4)
                               WHEN fld1 LIKE '%[0-9][0-9[0-9}[0-9]' THEN left(fld1, len(fld1) -  4)
                          END)
       FROM  #tbl1
    ), moreparts AS (
       SELECT fld1, year,
              sorter = CASE WHEN rest = 'FY'        THEN '4C'
                            WHEN rest LIKE '[0-9]Q' THEN left(rest, 1) + 'A'
                            WHEN rest LIKE '[0-9]H' THEN convert(char(1), 2*convert(int, left(rest, 1))) + 'B'
                       END
       FROM   pickapart
    )
    SELECT year, fld1, sorter
    FROM   moreparts
    ORDER  BY year, sorter
    The trim function was added in SQL 2017. On older version use rtrim and ltrim instead.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se


    Saturday, May 23, 2020 11:37 AM
  • see my code

    create table #tbl1 
    (id int identity(1,1),
    fld1 varchar(50),
    )

    tell me how to restructure my above code where i tried but could not do it. thanks

    Good day Sudip,

    This is a perfect scenario where you should not "restructure" the SELECT query, but "restructure" the table structure.

    The difficult which you have to parse the data is not even the worse issue here. You should really understand what are the implications on the server side starting with memory and CPU and storage size using this table.

    You can for example use 2 separate columns for the year and for the part of the year and use integers for the data instead of strings. In addition if this is the order which you always use then you should have indexes which fit that order instead of sorting the data on-the-fly each time you execute a query.

    In the meantime, I think Erland gave you a query for your current request. If his answer solve your need then mark it please and if not then please explain what is your requested result


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, May 23, 2020 12:59 PM
    Moderator
  • If ‘2011FY’ was a mistake and can be corrected to ‘2011 FY’, then check this ordering too:

    select *
    from #tbl1
    order by
      case 
    	when fld1 like '[12]Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '1' + left(fld1, 1)
    	when fld1 like '[1]H [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '2' + left(fld1, 1)
    	when fld1 like '[34]Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '3' + left(fld1, 1)
    	when fld1 like '[2]H [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '4' + left(fld1, 1)
    	when fld1 like '[0-9][0-9][0-9][0-9] FY' then left(fld1, 4) + '50'
      end
    


    • Edited by Viorel_MVP Saturday, May 23, 2020 2:40 PM
    • Marked as answer by Sudip_inn Tuesday, May 26, 2020 6:14 AM
    • Unmarked as answer by Sudip_inn Tuesday, May 26, 2020 6:14 AM
    Saturday, May 23, 2020 2:30 PM
  • select id,  fld1  
    from #tbl1 
    Order by Cast(Ltrim(rtrim(replace(replace(replace(replace(replace(replace(replace(fld1,'1Q',''),'2Q',''),'3Q',''),'4Q','')
    ,'FY',''),'1H',''),'2H','')))
    +'-'+
    CASE WHEN Left(fld1,2) = '1Q' THEN '01'
    WHEN Left(fld1,2) = '2Q' THEN '04'
    WHEN Left(fld1,2) = '3Q' THEN '07'
    WHEN Left(fld1,2) = '4Q' THEN '10' 
    WHEN Left(fld1,2) = '1H' THEN '06' 
    WHEN Left(fld1,2) = '2H' THEN '12' 
    WHEN Right(fld1,2)= 'FY' THEN '12' 
    Else NULL END +  
    Case   WHEN Right(fld1,2) = 'FY' THEN '-31'
    else  '-01' end  as DATE)

    • Marked as answer by Sudip_inn Tuesday, May 26, 2020 6:14 AM
    Saturday, May 23, 2020 7:06 PM
    Moderator
  • If ‘2011FY’ was a mistake and can be corrected to ‘2011 FY’, then check this ordering too:

    select *
    from #tbl1
    order by
      case 
    	when fld1 like '[12]Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '1' + left(fld1, 1)
    	when fld1 like '[1]H [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '2' + left(fld1, 1)
    	when fld1 like '[34]Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '3' + left(fld1, 1)
    	when fld1 like '[2]H [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '4' + left(fld1, 1)
    	when fld1 like '[0-9][0-9][0-9][0-9] FY' then left(fld1, 4) + '50'
      end
    In this case (if you have space between the two parts of the string) then you should solve the issue using PARSENAME which is the best regarding performance in such cases (unfortunately it does not cover the current sample data)

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, May 23, 2020 9:16 PM
    Moderator
  • You can create a DateTable for this purpose and can use it easily.

    CREATE TABLE [dbo].[DateTable]
    ( [DateKey] INT primary key, 
    [Date] DATETIME,
    [DayOfMonth] VARCHAR(2), -- Field will hold day number of Month
    [DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday 
    [DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month
    [DayOfWeekInYear] VARCHAR(2),
    [DayOfQuarter] VARCHAR(3),
    [DayOfYear] VARCHAR(3),
    [WeekOfMonth] VARCHAR(1),-- Week Number of Month 
    [WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter
    [WeekOfYear] VARCHAR(2),--Week Number of the Year
    [Month] VARCHAR(2), --Number of the Month 1 to 12
    [MonthName] VARCHAR(9),--January, February etc
    [MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter
    [Quarter] CHAR(1),
    [QuarterName] VARCHAR(9),--First,Second..
    [Year] CHAR(4),-- Year value of Date stored in Row
    [YearName] CHAR(7), --CY 2012,CY 2013
    [MonthYear] CHAR(10), --Jan-2013,Feb-2013
    [MMYYYY] CHAR(6),
    )
    GO

     

    DECLARE @StartDate DATETIME = '01/01/2020' --Starting value of Date Range
    DECLARE @EndDate DATETIME = '01/01/2030' --End Value of Date Range

    --Temporary Variables To Hold the Values During Processing of Each Date of Year
    DECLARE
    @DayOfWeekInMonth INT,
    @DayOfWeekInYear INT,
    @DayOfQuarter INT,
    @WeekOfMonth INT,
    @CurrentYear INT,
    @CurrentMonth INT,
    @CurrentQuarter INT

    /*Table Data type to store the day of week count for the month and year*/
    DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT)

    INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
    INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
    INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
    INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
    INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
    INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
    INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)

    --Extract and assign various parts of Values from Current Date to Variable

    DECLARE @CurrentDate AS DATETIME = @StartDate
    SET @CurrentMonth = DATEPART(MM, @CurrentDate)
    SET @CurrentYear = DATEPART(YY, @CurrentDate)
    SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)

    /********************************************************************************************/
    --Proceed only if Start Date(Current date ) is less than End date you specified above

    WHILE @CurrentDate < @EndDate
    BEGIN

    /*Begin day of week logic*/

             /*Check for Change in Month of the Current date if Month changed then 
              Change variable value*/
    IF @CurrentMonth != DATEPART(MM, @CurrentDate) 
    BEGIN
    UPDATE @DayOfWeek
    SET MonthCount = 0
    SET @CurrentMonth = DATEPART(MM, @CurrentDate)
    END

            /* Check for Change in Quarter of the Current date if Quarter changed then change 
             Variable value*/

    IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
    BEGIN
    UPDATE @DayOfWeek
    SET QuarterCount = 0
    SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
    END

    IF @CurrentYear != DATEPART(YY, @CurrentDate)
    BEGIN
    UPDATE @DayOfWeek
    SET YearCount = 0
    SET @CurrentYear = DATEPART(YY, @CurrentDate)
    END

    UPDATE @DayOfWeek
    SET 
    MonthCount = MonthCount + 1,
    QuarterCount = QuarterCount + 1,
    YearCount = YearCount + 1
    WHERE DOW = DATEPART(DW, @CurrentDate)

    SELECT
    @DayOfWeekInMonth = MonthCount,
    @DayOfQuarter = QuarterCount,
    @DayOfWeekInYear = YearCount
    FROM @DayOfWeek
    WHERE DOW = DATEPART(DW, @CurrentDate)

    /* Populate Your Date Table with values*/

    INSERT INTO [dbo].[DateTable]
    SELECT

    CONVERT (char(8),@CurrentDate,112) as DateKey,
    @CurrentDate AS Date,
    DATEPART(DD, @CurrentDate) AS DayOfMonth,
    DATENAME(DW, @CurrentDate) AS DayName,
    @DayOfWeekInMonth AS DayOfWeekInMonth,
    @DayOfWeekInYear AS DayOfWeekInYear,
    @DayOfQuarter AS DayOfQuarter,
    DATEPART(DY, @CurrentDate) AS DayOfYear,
    DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR, _
    DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR, _
    DATEPART(YY, @CurrentDate))) AS WeekOfMonth,
    (DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), _
    @CurrentDate) / 7) + 1 AS WeekOfQuarter,
    DATEPART(WW, @CurrentDate) AS WeekOfYear,
    DATEPART(MM, @CurrentDate) AS Month,
    DATENAME(MM, @CurrentDate) AS MonthName,
    CASE
    WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1
    WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2
    WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3
    END AS MonthOfQuarter,
    DATEPART(QQ, @CurrentDate) AS Quarter,
    CASE DATEPART(QQ, @CurrentDate)
    WHEN 1 THEN 'First'
    WHEN 2 THEN 'Second'
    WHEN 3 THEN 'Third'
    WHEN 4 THEN 'Fourth'
    END AS QuarterName,
    DATEPART(YEAR, @CurrentDate) AS Year,
    'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS YearName,
    LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR, _
    DATEPART(YY, @CurrentDate)) AS MonthYear,
    RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) + _
    CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY
    SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
    END

    You table is ready to play and sort as you wish.

    Saturday, May 23, 2020 11:29 PM
  • Hi Sudip_inn,

    Do the answers above help you? Please feel free to let us know if you have any other question.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 25, 2020 2:49 AM
  • @Jingyang Li Perfect script....thank you sir.
    Tuesday, May 26, 2020 6:15 AM
  • Worked.....but Regex not clear. thanks
    Tuesday, May 26, 2020 6:16 AM
  • @Erland Sommarskog    Perfect Thank you Sir.
    Tuesday, May 26, 2020 6:19 AM
  • one more changes has come

    INSERT INTO #tbl1 VALUES
    ('1Q 2014'),
    ('2Q 2014'),
    ('2012 FY'),
    ('1Q 2013'),
    ('2Q 2013'),
    ('4Q 2014'),
    ('4Q 2013'),
    ('2013 FY'),
    ('2011FY'),
    ('3Q 2014'),
    ('3Q 2013'),
    ('2010 FY'),
    ('2014 FY'),
    ('1H 2013'),
    ('2H 2013'),
    ('1H 2014'),
    ('2H 2014'),
    ('4Q 2013 53 Weeks'),
    ('2013 FY 53 Weeks'),
    ('Transition Period 2014')

    there should be new period called 

    4Q 2013 53 Weeks

    2013 FY 53 Weeks

    Transition Period 2014

    1) 4Q 2013 53 Weeks

    this 53 weeks will come after 4Q 2013 because it has 4Q 2013. if 53 weeks would be look like 

    3Q 2014 53 Weeks then it would come after 3Q 2014. how to accommodate this?

    2) Transition Period 2014 this Transition Period has 2014 so it will appear at end of all period of 2014.

    please give me new script which help me to display Transition Period & 53 Weeks in order. thanks


    • Edited by Sudip_inn Tuesday, May 26, 2020 8:07 AM
    Tuesday, May 26, 2020 8:04 AM
  • one more changes has come

    INSERT INTO #tbl1 VALUES
    ('1Q 2014'),
    ('2Q 2014'),
    ('2012 FY'),
    ('1Q 2013'),
    ('2Q 2013'),
    ('4Q 2014'),
    ('4Q 2013'),
    ('2013 FY'),
    ('2011FY'),
    ('3Q 2014'),
    ('3Q 2013'),
    ('2010 FY'),
    ('2014 FY'),
    ('1H 2013'),
    ('2H 2013'),
    ('1H 2014'),
    ('2H 2014'),
    ('4Q 2013 53 Weeks'),
    ('2013 FY 53 Weeks'),
    ('Transition Period 2014')

    there should be new period called 

    4Q 2013 53 Weeks

    2013 FY 53 Weeks

    Transition Period 2014

    1) 4Q 2013 53 Weeks

    this 53 weeks will come after 4Q 2013 because it has 4Q 2013. if 53 weeks would be look like 

    3Q 2014 53 Weeks then it would come after 3Q 2014. how to accommodate this?

    2) Transition Period 2014 this Transition Period has 2014 so it will appear at end of all period of 2014.

    please give me new script which help me to display Transition Period & 53 Weeks in order. thanks


    Extend the available scripts or try this query too:

    select *
    from #tbl1
    order by
      case 
    	when fld1 like '1Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '01'
    	when fld1 like '1Q [0-9][0-9][0-9][0-9] %Weeks' then substring(fld1, 4, 4) + '02'
    	when fld1 like '2Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '03' 
    	when fld1 like '2Q [0-9][0-9][0-9][0-9] %Weeks' then substring(fld1, 4, 4) + '04' 
    	when fld1 like '1H [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '05'
    	when fld1 like '3Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '06'
    	when fld1 like '3Q [0-9][0-9][0-9][0-9] %Weeks' then substring(fld1, 4, 4) + '07'
    	when fld1 like '4Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '08'
    	when fld1 like '4Q [0-9][0-9][0-9][0-9] %Weeks' then substring(fld1, 4, 4) + '09'
    	when fld1 like '2H [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '10'
    	when fld1 like '[0-9][0-9][0-9][0-9] FY' or fld1 like '[0-9][0-9][0-9][0-9]FY' then left(fld1, 4) + '11'
    	when fld1 like '[0-9][0-9][0-9][0-9] FY %Weeks' then left(fld1, 4) + '12'
    	when fld1 like 'Transition Period [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '13'
      end

    But if it is possible to have several patterns like ‘4Q 2013 XX Weeks’ with different number of weeks, then the query can be adjusted.

    It assumes that ‘2013 FY 53 Weeks’ should appear after ‘2013 FY’ or after ‘2013FY’ and before ‘Transaction Period 2013’ .





    • Edited by Viorel_MVP Tuesday, May 26, 2020 8:44 AM
    Tuesday, May 26, 2020 8:34 AM
  • i just change data in table and now your script is not working

    error is Incorrect syntax near the keyword 'select'.

    create table #tbl1 
    (
    id int identity(1,1),
    fld1 varchar(50),
    )
    
    INSERT INTO #tbl1 VALUES
    ('1Q 2014'),
    ('2Q 2014'),
    ('2012 FY'),
    ('1Q 2013'),
    ('2Q 2013'),
    ('4Q 2014'),
    ('4Q 2013'),
    ('2013 FY'),
    ('2011FY'),
    ('3Q 2014'),
    ('3Q 2013'),
    ('2010 FY'),
    ('2014 FY'),
    ('1H 2013'),
    ('2H 2013'),
    ('1H 2014'),
    ('2H 2014'),
    ('4Q 2014 53 Weeks'),
    ('2014 FY 53 Weeks'),
    
    
    
    select *
    from #tbl1
    order by
      case 
    	when fld1 like '1Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '01'
    	when fld1 like '1Q [0-9][0-9][0-9][0-9] %Weeks' then substring(fld1, 4, 4) + '02'
    	when fld1 like '2Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '03' 
    	when fld1 like '2Q [0-9][0-9][0-9][0-9] %Weeks' then substring(fld1, 4, 4) + '04' 
    	when fld1 like '1H [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '05'
    	when fld1 like '3Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '06'
    	when fld1 like '3Q [0-9][0-9][0-9][0-9] %Weeks' then substring(fld1, 4, 4) + '07'
    	when fld1 like '4Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '08'
    	when fld1 like '4Q [0-9][0-9][0-9][0-9] %Weeks' then substring(fld1, 4, 4) + '09'
    	when fld1 like '2H [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '10'
    	when fld1 like '[0-9][0-9][0-9][0-9] FY' or fld1 like '[0-9][0-9][0-9][0-9]FY' then left(fld1, 4) + '11'
    	when fld1 like '[0-9][0-9][0-9][0-9] FY %Weeks' then left(fld1, 4) + '12'
    	when fld1 like 'Transition Period [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '13'
      end
    
      drop table #tbl1

    another issue is RegEx is hard to understand. whyout regex can u do it?

    thanks



    • Edited by Sudip_inn Tuesday, May 26, 2020 8:58 AM
    Tuesday, May 26, 2020 8:56 AM
  • i just change data in table and now your script is not working

    error is Incorrect syntax near the keyword 'select'.

    create table #tbl1 
    (
    id int identity(1,1),
    fld1 varchar(50),
    )
    
    INSERT INTO #tbl1 VALUES
    ('1Q 2014'),
    ('2Q 2014'),
    ('2012 FY'),
    ('1Q 2013'),
    ('2Q 2013'),
    ('4Q 2014'),
    ('4Q 2013'),
    ('2013 FY'),
    ('2011FY'),
    ('3Q 2014'),
    ('3Q 2013'),
    ('2010 FY'),
    ('2014 FY'),
    ('1H 2013'),
    ('2H 2013'),
    ('1H 2014'),
    ('2H 2014'),
    ('4Q 2014 53 Weeks'),
    ('2014 FY 53 Weeks'),
    
    
    
    select *
    from #tbl1
    order by
      case 
    	when fld1 like '1Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '01'
    	when fld1 like '1Q [0-9][0-9][0-9][0-9] %Weeks' then substring(fld1, 4, 4) + '02'
    	when fld1 like '2Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '03' 
    	when fld1 like '2Q [0-9][0-9][0-9][0-9] %Weeks' then substring(fld1, 4, 4) + '04' 
    	when fld1 like '1H [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '05'
    	when fld1 like '3Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '06'
    	when fld1 like '3Q [0-9][0-9][0-9][0-9] %Weeks' then substring(fld1, 4, 4) + '07'
    	when fld1 like '4Q [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '08'
    	when fld1 like '4Q [0-9][0-9][0-9][0-9] %Weeks' then substring(fld1, 4, 4) + '09'
    	when fld1 like '2H [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '10'
    	when fld1 like '[0-9][0-9][0-9][0-9] FY' or fld1 like '[0-9][0-9][0-9][0-9]FY' then left(fld1, 4) + '11'
    	when fld1 like '[0-9][0-9][0-9][0-9] FY %Weeks' then left(fld1, 4) + '12'
    	when fld1 like 'Transition Period [0-9][0-9][0-9][0-9]' then right(fld1, 4) + '13'
      end
    
      drop table #tbl1

    another issue is RegEx is hard to understand. whyout regex can u do it?

    thanks



    Remove ',' before select in your script, or restore the missing lines.
    • Edited by Viorel_MVP Tuesday, May 26, 2020 9:09 AM
    Tuesday, May 26, 2020 9:04 AM