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 |