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!

you probably have some batchno without a "-" which ruins the rest of the function calls. you can use case when charindex('-', batchno) > 0 then ... else ... end instead — siggemannen, Jul 26 '23 at 12:02
Or `NULL` the value when it's `0`: `NULLIF(CHARINDEX('-', REVERSE(BATCHNO)),0)`. — Thom A, Jul 26 '23 at 12:03
@siggemannen Normally BATCHNO comes with 2 hyphens. I included now a `CASE WHEN charindex('-', batchno) = 2 THEN [Expression] ELSE NULL ` but I still get the same error message — AlexisPa, Jul 26 '23 at 12:14
That's not that charindex does. It returns position of first "-". If you wanna check for two - - , you should do something like: case when patindex('%[-]%[-]%', batchno) > 0 — siggemannen, Jul 26 '23 at 12:22
If you take the time to provide a [mre], and sample data and expected results, that will help us explain the problem better, AlexisPa . — Thom A, Jul 26 '23 at 12:33
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Jul 26 '23 at 12:45
@siggemannen it works now, thanks so much! I am still confused as to how the query without the subquery works perfectly well but when it is nested I get the error. If you post your comment as answer I'll mark it as the solution considering you were first to provide one that worked for me. — AlexisPa, Jul 26 '23 at 14:15
The query without the subquery _doesn't_ work very well: [same error message](https://dbfiddle.uk/9tZXujSC). Perhaps there is more to the story than you've told us and you could construct [a more accurate MCVE](https://stackoverflow.com/help/minimal-reproducible-example) for us - I suspect your view has a where clause or other additional logic that lets the simple query filter bad rows away first, but filters are applied later in the more complex query, so the expression is attempted on those rows _before_ they are filtered away. — Stuck at 1337, Jul 26 '23 at 17:38

Stuck at 1337 · Accepted Answer · 2023-07-26T13:05:30.977

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.

Charindex error invalid length in RIGHT function

1 Answers1