SQL Server Invalid length parameter passed to the LEFT or SUBSTRING function error?

3gtaxfhh 于 2023-03-07 发布在其他

关注(0)|答案(1)|浏览(157)

I have seen a few of these questions here but can't find something that applies to my code. Here is my snipet where I am getting the error:

left join 
    WORKQUEUE_INFO wqi
        on wqi.WORKQUEUE_ID = SUBSTRING(hxcmt.hx_comment,CHARINDEX('[',hxcmt.hx_comment)+1,(CHARINDEX(']',hxcmt.hx_comment)-CHARINDEX('[',hxcmt.hx_comment))-1)

HX_COMMENT returns values like this:

'Deferred until 09/13/2022 12:00 AM CDT on workqueue ADMIN-CATCH ALL QUEUE [203]'

So what I am trying to do is join on the value in the brackets in HX_COMMENT (203 from the above value)

Any ideas why this might be failing?

sql-server

来源：https://stackoverflow.com/questions/75653743/invalid-length-parameter-passed-to-the-left-or-substring-function-error

1条答案

按热度按时间

axr492tv1#

It becomes a small matter to trap/nullify zero values

Example

Declare @YourTable Table ([HX_COMMENT] varchar(150))  Insert Into @YourTable Values 
 ('... CDT on workqueue ADMIN-CATCH ALL QUEUE [203]')
,('... CDT on workqueue ADMIN-CATCH ALL QUEUE [999] other text')
,('... CDT on workqueue ADMIN-CATCH ALL QUEUE -- No Brackets')
 

Select A.*
      ,NewVal = substring([HX_COMMENT],P1+1,P2-P1-1)
 From  @YourTable A
 Cross Apply ( values (nullif(charindex('[',[HX_COMMENT]),0),nullif(charindex(']',[HX_COMMENT]),0) ) ) B(P1,P2)

Results

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server Invalid length parameter passed to the LEFT or SUBSTRING function error?

1条答案

相关问题

热门标签

最新问答