SQL Server 2022

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

CREATE TABLE dbo.TABLE_A
(BATCHID int, BATCHNO varchar(32), OPENDATE date)

INSERT dbo.TABLE_A VALUES
(1, 'X-ASDF-054','20230102'),
(2, 'X-ASDF-033','20230105'),
(3, 'X-QWER-056','20230112'),
(99,'Bad Data',  '20230116');
4 rows affected

SELECT
BATCHID, BATCHNO,  OPENDATE, 
RIGHT(LEFT(BATCHNO, LEN(BATCHNO) - LEN(RIGHT(BATCHNO, 
  CHARINDEX('-', REVERSE(BATCHNO)) - 1)) - 1), 
  CHARINDEX('-', REVERSE(LEFT(BATCHNO, 
  LEN(BATCHNO) - LEN(RIGHT(BATCHNO, CHARINDEX('-', 
  REVERSE(BATCHNO)) - 1)) - 1))) - 1) 
AS PRODUCTNAME
FROM dbo.TABLE_A;
Msg 536 Level 16 State 2 Line 1
Invalid length parameter passed to the RIGHT function.

SELECT
BATCHID, BATCHNO,  OPENDATE, 
CASE WHEN BATCHNO LIKE N'%[-]%[-]%' THEN
RIGHT(LEFT(BATCHNO, LEN(BATCHNO) - LEN(RIGHT(BATCHNO, 
  CHARINDEX('-', REVERSE(BATCHNO)) - 1)) - 1), 
  CHARINDEX('-', REVERSE(LEFT(BATCHNO, 
  LEN(BATCHNO) - LEN(RIGHT(BATCHNO, CHARINDEX('-', 
  REVERSE(BATCHNO)) - 1)) - 1))) - 1) 
END
AS PRODUCTNAME
FROM dbo.TABLE_A;


BATCHID
BATCHNO
OPENDATE
PRODUCTNAME


1
X-ASDF-054
2023-01-02
ASDF

2
X-ASDF-033
2023-01-05
ASDF

3
X-QWER-056
2023-01-12
QWER

99
Bad Data
2023-01-16
null


SELECT BATCHID, BATCHNO,  OPENDATE, 
  JSON_VALUE('["' + REPLACE(BATCHNO,'-','","') + '"]','$[1]')
  AS PRODUCTNAME
FROM dbo.TABLE_A;


BATCHID
BATCHNO
OPENDATE
PRODUCTNAME


1
X-ASDF-054
2023-01-02
ASDF

2
X-ASDF-033
2023-01-05
ASDF

3
X-QWER-056
2023-01-12
QWER

99
Bad Data
2023-01-16
null

BATCHID	BATCHNO	OPENDATE	PRODUCTNAME
1	X-ASDF-054	2023-01-02	ASDF
2	X-ASDF-033	2023-01-05	ASDF
3	X-QWER-056	2023-01-12	QWER
99	Bad Data	2023-01-16	null