Weird issue with LEFT and CHARSTRING

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Weird issue with LEFT and CHARSTRING

Author

Topic

Kanwulf
Starting Member

11 Posts

Posted - 2009-04-10 : 07:27:57

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))

Thanks,
Johnny

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-10 : 14:52:57

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

Kanwulf
Starting Member

11 Posts

Posted - 2009-04-11 : 06:44:25

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources