SQL Server How do I remove the last values by delimiter

vvppvyoh 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(133)

Hi I am trying to remove the delimiter along with the value(s) in the sku column. Any help would be appreciated!

TABLE
| container | sku | qty |
| ------------ | ------------ | ------------ |
| YMLU1234567 | 534164 | 1877 |
| YMLU1234567 | 987876 | 554 |
| YMLU1234567 | 987876-1 | 91 |
| YMLU1234567 | 987877 | 841 |
| YMLU1234567 | 987877-1 | 106 |

Current code

SELECT
    [so].[OrderGroup] as container,
    REVERSE(LEFT(REVERSE([so].[CustomerOrderNumber]),
    CHARINDEX(' ', REVERSE([so].[CustomerOrderNumber]) + ' ') - 1)) AS sku,
    sum([so].[QtyShipped]) as AllocatedQty
FROM [server].[dbo].[table] as so
WHERE [so].Status <> 0
AND [so].CustomerName ='alpha'
AND [so].[OrderGroup] ='YMLU1234567'
GROUP BY  [so].[OrderGroup], REVERSE(LEFT(REVERSE([so].[CustomerOrderNumber]),
          CHARINDEX(' ', REVERSE([so].[CustomerOrderNumber]) + ' ') - 1))

sql-server

来源：https://stackoverflow.com/questions/77484215/how-do-i-remove-the-last-values-by-delimiter

1条答案

按热度按时间

kq0g1dla1#

Please try the following solution.

All the work is done in the CROSS APPLY . It outputs a clean sku.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (container VARCHAR(20),sku VARCHAR(10), qty INT);
INSERT @tbl (container, sku, qty) VALUES
('YMLU1234567', '534164',   1877),
('YMLU1234567', '987876',   554 ),
('YMLU1234567', '987876-1', 91  ),
('YMLU1234567', '987877',   841 ),
('YMLU1234567', '987877-1', 106 )
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '-';

SELECT container, t1.sku, SUM(qty) AS AllocatedQty
FROM @tbl
CROSS APPLY (SELECT LEFT(sku, CHARINDEX(@separator, sku + @separator) - 1)) AS t1(sku)
GROUP BY container, t1.sku;

Output

container	sku	AllocatedQty
YMLU1234567	534164	1877
YMLU1234567	987876	645
YMLU1234567	987877	947

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server How do I remove the last values by delimiter

1条答案

相关问题

热门标签

最新问答