Item order in Correct Format

Question

here is my query and query result also i pest what result what i want

SELECT StockGroup [Stock Group], [Item Description], [Balance Bag] [In Stock], [UOM 1], [Balance Stock] [In Stock], [UOM 2], [Usage Per Day], [No of Days In Stock], [Lead Time], [Tolerance Time], [Buffer In Stock], [Required In Stock], [Incoming Qty.], AcceptedDate, [PO Generated], CASE WHEN ISNULL([Incoming Qty.],0)=0 AND AcceptedDate IS NULL AND ([Balance Stock]) < ([Usage Per Day] * ([Lead Time])) THEN 2 WHEN ISNULL([Incoming Qty.],0)=0 AND AcceptedDate IS NULL AND ([Balance Stock]) < ([Usage Per Day] * ([Lead Time] + [Tolerance Time])) THEN 1 WHEN ISNULL([Incoming Qty.],0)> 0 AND AcceptedDate IS NULL AND ([Balance Stock] + ISNULL([Incoming Qty.],0)) < ([Usage Per Day] * ([Lead Time] + [Tolerance Time])) THEN 4 WHEN ISNULL([Incoming Qty.],0)>= 0 AND AcceptedDate IS NOT NULL AND ([Balance Stock] + ISNULL([Incoming Qty.],0)) < ([Usage Per Day] * ([Lead Time] + [Tolerance Time])) THEN 4 WHEN ISNULL([Incoming Qty.],0)>= 0 AND AcceptedDate IS NOT NULL AND ([CONFIRM INComingStock ONTIME]+[CONFIRM INComingStock LATETIME]+[Incoming Qty.]) < ([Usage Per Day] * ([Lead Time] + [Tolerance Time])) THEN 4 WHEN (ISNULL([CONFIRM INComingStock ONTIME],0) + ISNULL([CONFIRM INComingStock LATETIME],0)) > 0 THEN CASE WHEN ISNULL([CONFIRM INComingStock LATETIME],0) = 0 AND ([CONFIRM INComingStock ONTIME] + [Incoming Qty.]) > ([Required In Stock]) THEN 5 WHEN ISNULL([CONFIRM INComingStock ONTIME],0) + ISNULL([CONFIRM INComingStock LATETIME],0) + [Incoming Qty.] > ([Required In Stock]) THEN 6 END ELSE 0 END [Status] FROM MRPBasedOnItemConsumption('" + cmbPurchaseType.SelectedValue.ToString() + "','" + rcmbCompany.SelectedValue.ToString() + "','" + Convert.ToDateTime(dtpFromDate.Value).ToString("yyyyMMdd") + "') ORDER BY StockGroup, [Item Description]

Result i want is below

Answer 1

Hi @Hiteshkumar Patel,

A minimal reproducible example is not provided.

Here is a conceptual solution for you based on tokenization via SQL Server's XML and XQuery functionality. It will work starting from SQL Server 2017 onwards (due to TRIM() function extended functionality).

We will tokenize the Item_Description column as XML. After that we will retrieve 7th token, cast it as integer, and sort by it.

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Item_Description VARCHAR(200));
INSERT @tbl VALUES
('MATT BOPP FILM [ 20MIC X 575MM ]'),
('MATT BOPP FILM [ 20MIC X 1115MM ]'),
('MATT BOPP FILM [ 20MIC X 1220MM ]'), 
('MATT BOPP FILM [ 20MIC X 675MM ]');
-- DDL and sample data population, end
DECLARE @separator CHAR(1) = SPACE(1);
SELECT *
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
    REPLACE(Item_Description, @separator, ']]></r><r><![CDATA[') + 
    ']]></r></root>' AS XML)) AS t1(c)
CROSS APPLY (SELECT TRY_CAST(TRIM('M' FROM c.value('(/root/r[7]/text())[1]', 'VARCHAR(10)')) AS INT)) AS t2(x)
ORDER BY x ASC;

Answer 2

Hi @Hiteshkumar Patel

Try this:

ORDER BY 
StockGroup,
CAST(SUBSTRING([Item Description],PATINDEX('%[0-9]%', [Item Description]),2)AS int),
CAST(REPLACE(SUBSTRING([Item Description],CHARINDEX('X',[Item Description])+1,5),'M','')AS int)

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

your issue is that the order by column is a string. for numeric string values to sort correctly, they need the leading zeros:

MATT BOPP FILM [20MIC X 575MM]
MATT BOPP FILM [20MIC X 1115MM]

s/b

MATT BOPP FILM [20MIC X 0575MM]
MATT BOPP FILM [20MIC X 1115MM]

the best option is to have a sort override column. which you can update with the correct sort description

order by StockGroup, coalesce(sortOveride,[Item Description])