Charindex error invalid length in RIGHT function

Question

I have a SQL Server View similar to this:

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 TABLE_A

that creates results like this:

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

and so on.

When I query the table like this

SELECT PRODUCTNAME, MIN(OPENDATE) AS MIN_OPENDATE 
FROM VIEW_X
GROUP BY PRODUCTNAME

everything works as expected. But when the query is nested similar to this (which I can't avoid nor influence due to the regulated environment I'm working in):

SELECT PRODUCTNAME
FROM (
    SELECT PRODUCTNAME, MIN(OPENDATE) AS MIN_OPENDATE
    FROM VIEW_X
    GROUP BY PRODUCTNAME
) AS sub_query
WHERE PRODUCTNAME = 'ASDF'

I get the error

Invalid length parameter passed to the RIGHT function.

and I can't figure out why nor how to solve it. I can influence how to create the view and the subquery but I can't influence the nesting of the function. I am looking for a solution that gives me all entries in which the product name is specified using the PRODUCTNAME column. Thanks a lot in advance!

Answer 1

The problem is that the big ugly expression you're using to parse parts out of a string may run against rows where that expression will fail (e.g. a BATCHNO with no - characters).

Instead, do this:

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) 
AS PRODUCTNAME
FROM dbo.TABLE_A;

Of course a much simpler approach in SQL Server 2016+ is (lifted from this great answer):

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

Example at db<>fiddle.