Substring Error

Question

Post reply

Substring Error

Shelley Allen

SSCrazy

Points: 2146
More actions
March 20, 2006 at 10:02 am

#93671

Hi All,
I have a stored procedure/job that runs daily and I have been receiving the below message:
Executed as user: ACU\sqladmin. Invalid length parameter passed to the substring function. [SQLSTATE 42000] (Error 536) The statement has been terminated. [SQLSTATE 01000] (Error 3621) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Invalid length parameter passed to the substring function. [SQLSTATE 42000] (Error 536) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
Any Ideas?
Here is my procedure I run.
CREATE PROCEDURE [ACU_Blank_Name_Update] AS
/*Replace all blank first name fields with the first name in the contact field*/
update contact2
set contact2.uexfield1 = substring(contact1.contact,1,charindex(' ',contact)-1)
from contact1, contact2
where contact1.accountno = contact2.accountno
and contact1.contact <> ' '
and (contact2.uexfield1 is null
or contact2.uexfield1 = ' ')
/*Replace all blank dear field with the first name in the contact field where the dear field is blank*/
update contact1
set dear = substring(contact,1,charindex(' ',contact)-1)
where contact1.contact <> ' '
and dear is null or dear = ' '
GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246795 More actions · Answer 1

This code reproduces the error - could it be the problem?

declare @contact varchar(10)

set @contact = 'abbbbbbbbc '

select substring(@contact,1,charindex(' ',@contact)-1)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

David le Quesne SSCertifiable Points: 5229 More actions · Answer 2

Hi,

you could use a searched case function to check whether there was a space in the contact1.contact field. If there was no space you could either set contact2.uexfield1 to the entire contact1.contact field as below, or set contact2.uexfield1 to NULL

update contact2

set contact2.uexfield1 = case when charindex(' ',contact)>0 then substring(contact1.contact,1,charindex(' ',contact)-1) else contact1.contact end

...

David

If it ain't broke, don't fix it...

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 3

Have you thought about using ltrim to remove leading spaces, if any?

set contact2.uexfield1 = ltrim(contact1.contact)

Or, am I missing something?

Shelley Allen SSCrazy Points: 2146 More actions · Answer 4

Thank you all so much. I love using CASE. I can't believe I forgot about that.