Invalid length parameter passed to the SUBSTRING function

The error 'Invalid length parameter passed to the SUBSTRING function' occurs in SQL.  The code with the problem is below.
RTRIM(Substring('000000000000000',1,15 - LEN(pid.Person_Identifier_Value))                   + pid.Person_Identifier_Value)      AS 'Consumer Data ID'
Person_Idendifier_Value is not null for any of the records.  The length of each value is less than 15.  15 - the length is a positive number in all cases.  The SQL does multiple joins.  I thought of building a temp table and getting the values from there.  I do not want to add too much overhead to the SQL.  
edbrinkmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Lee SavidgeCommented:
Are you trying to zero pad the ID?

Lee
0
 
edbrinkmAuthor Commented:
yes.  
0
 
Lee SavidgeCommented:
Try this. I don't think you've got the right syntax.

Lee
right('000000000000000' + pid.Person_Identifier_Value, 15) AS 'Consumer Data ID'

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Lee SavidgeCommented:
As long as pid.person_identifier_value is a varchar or nvarchar. If not, do a cast.

Lee
right('000000000000000' + cast(pid.Person_Identifier_Value as nvarchar(15)), 15) AS 'Consumer Data ID'

Open in new window

0
 
edbrinkmAuthor Commented:
Thank you
0
 
Lee SavidgeCommented:
No problem.

Lee
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.