sql - Invalid length parameter passed to the LEFT or SUBSTRING function on update statement

Question

While I am not getting any error on running a SQL query using my substring statement, I am getting an error while trying to do the same using update statement.

I am not getting any error while running this:

select distinct o.ID as ClientID,d.ClientName,e.[name] as ClientName
from  Core.dbo.DE_Organization o
inner join [ld].[Me_Claims] d  on SUBSTRING(o.Data_Store,44,ABS(CHARINDEX(';Us',o.Data_Store,34)-44)) = d.ClientName
left join [Consolidated_QA].[dbo].[employer] e
on o.ID=e.leg_emr_id
where o.Data_Store LIKE 'Data Source=SQL%' and d.SourceSystem='M'
order by d.ClientName

I am getting error "Invalid length parameter passed to the LEFT or SUBSTRING function" while running this:

update [Dev].[ld].[Me_Claims]
SET ClientID=o.ID,
ClientName=e.[Name]
FROM Core.dbo.DE_Organization o
    inner join [ld].[Me_Claims] d  on SUBSTRING(o.Data_Store,44,ABS(CHARINDEX(';Us',o.Data_Store,34)-44)) = d.ClientName
    left join [Consolidated_QA].[dbo].[employer] e
    on o.ID=e.leg_emr_id
    where o.Data_Store LIKE 'Data Source=SQL%' and d.SourceSystem='M'

Any idea what is causing this?

question from:https://stackoverflow.com/questions/65621392/invalid-length-parameter-passed-to-the-left-or-substring-function-on-update-stat

Answer 1

Waitting for answers