Invalid length parameter passed to the LEFT or SUBSTRING function in row concatenation

Question

i'm getting "Invalid length parameter passed to the LEFT or SUBSTRING function" error on this function, it takes numbers from strings like "Abracadabra {34}" or doesn't take if string has no "{}". I have a "CASE", but it seems like it doesn't work properly. Certainly it works perfectly with typical case - "Abracadabra {34}", but because of complicated condition in query i can't check this function with another cases. Maybe you could find mistake, just by taking a look. Please help me.

CREATE FUNCTION func (
@nidop int
)
RETURNS varchar
BEGIN
DECLARE @sreturn varchar(1000);
SET @sreturn = '';
 SELECT 
 @sreturn = CASE CHARINDEX('{', wp.description) 
 WHEN 0 THEN @sreturn
 ELSE
 @sreturn + SUBSTRING(wp.description, CHARINDEX('{', wp.description) + 1,
 CHARINDEX('}', wp.description) - CHARINDEX('{', wp.description) - 1) + ','
 END 
 FROM
 (/*some select*/) ttt, RB_WORKPLACE wp
 WHERE wp.classified = ttt.ap 
ORDER BY CASE WHEN CHARINDEX('{', wp.[description]) > 0 THEN 
CONVERT(INT, 
SUBSTRING(wp.[description],
          CHARINDEX('{', wp.[description]) + 1,
          CHARINDEX('}', wp.[description]) - CHARINDEX('{', wp.[description]) - 1)
 )
 ELSE 0 
END;
 SET @sreturn = SUBSTRING(@sreturn, 1, LEN(@sreturn) - 1)
 RETURN @sreturn;
END;

I will try to give more information about issue. In this function i'm trying to migrate another function from Oracle:

create or replace function func(nidop in number) return varchar2 as
  sreturn varchar2(1000);  
begin
  select listagg(to_number(substr(wp.description, instr(wp.description, '{') + 1,
                                   instr(wp.description, '}') - instr(wp.description, '{') - 1)), ',') within group(order by to_number(substr(wp.description, instr(wp.description, '{') + 1, instr(wp.description, '}') - instr(wp.description, '{') - 1)))
    into sreturn
    from (/*some select*/) ttt, workplace wp
   where wp.classified = ttt.ap;
    return sreturn;

exception
  when others then
    return null;
end func;

Temporaly I changed this not-mine query, to "xml path('')" like query, thanks all for help. Only one item was because of returning varchar instead of varchar(50) for example.(In my case)

Answer 1

The error is coming from your ORDER BY clause. You do not have it wrapped in a condition (CASE or IIF) like you have in the SELECT. Change your ORDER BY to be the following:

 ORDER BY CASE WHEN CHARINDEX('{', wp.[description]) > 0 THEN 
 CONVERT(INT, 
    SUBSTRING(wp.[description],
              CHARINDEX('{', wp.[description]) + 1,
              CHARINDEX('}', wp.[description]) - CHARINDEX('{', wp.[description]) - 1)
     )
     ELSE 0 
    END;

Also, it is not a good idea to specify the return type as simply VARCHAR without a length. The default width of a VARCHAR / NVARCHAR is either 1 or 30, depending on the context in which it is being stated. Since your local variable is declared as VARCHAR(1000), you should use the same 1000 for the declaration of the output type.