Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Weird issue with LEFT and CHARSTRING
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

11 Posts

Posted - 04/10/2009 :  07:27:57  Show Profile  Reply with Quote
select LEFT(null,CHARINDEX('.',NULL))

On some servers it works, on some it returns this error:

Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the left function.

The issue is observed mainly on 64 bit machines. The issue doesn't reproduce consistently (might be the case where 10 executions fail and the next 3 complete successfully and return null).

I am not trying to find a solution for it, I am jut trying to understand the root cause of the issue.

Strangely this works:
select LEFT(null,isnull(CHARINDEX('.',NULL),null))


Flowing Fount of Yak Knowledge

1635 Posts

Posted - 04/10/2009 :  14:52:57  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
root cause is NULL. what servers? SQL EXPRESS vs Enterprice? SQL 2005 , SQL 2000? All with the same Service pack and all?

If you don't have the passion to help people, you have no passion

Edited by - yosiasz on 04/10/2009 14:56:21
Go to Top of Page

Starting Member

11 Posts

Posted - 04/11/2009 :  06:44:25  Show Profile  Reply with Quote
We use only Sql Server 2005

The issue occurs on following servers:

Server1: 9.00.4035.00 SP3 Standard Edition (64-bit)
Server2: 9.00.3152.00 SP2 Standard Edition (64-bit)
Also we have a 32 bit server where it fails, but it's being patched rt now

The issue does not seem to occur on:

Server 4 9.00.4035.00 SP3 Standard Edition
Server 5 9.00.3228.00 SP2 Developer Edition

So I think it's not version related.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000