SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE stock (
  "Item" VARCHAR(60),
  "Qty" INTEGER
);
INSERT INTO stock
  ("Item", "Qty")
VALUES
  ('00001', '30'),
  ('This item is discontinued"00002', '20'),
  ('Just some random comment"00003', '10'),
  ('There can be any number of comment""Like this"00004', '20');
4 rows affected

SELECT RIGHT(Item, CHARINDEX('"', REVERSE(CONCAT('"', Item))) - 1) AS Item, Qty
FROM stock


Item
Qty


00001
30

00002
20

00003
10

00004
20