469,077 Members | 1,393 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,077 developers. It's quick & easy.

LEFT and string function

select left('Hello World /Ok',charindex('/','Hello World /Ok')-1)
Hello World
That works fine.

However I got an error message:
select left('Hello World Ok',charindex('/','Hello World Ok')-1)
Instead of:
'Hello World Ok'

I get:
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

Microsoft Doc incorrectly says:
"LEFT ( character_expression , integer_expression )
integer_expression
Is a positive whole number. If integer_expression is negative, a null
string is returned."

Is there an easier solutoin using left or any other string function
instead of using a case statement?
Also, charindex('/','Hello World Ok') should return NULL instead 0 so
that we can use isnull function.
Thanks.

Nov 3 '06 #1
2 7812
On 3 Nov 2006 02:45:44 -0800, ot*******@yahoo.com wrote:

(snip)
>Microsoft Doc incorrectly says:
"LEFT ( character_expression , integer_expression )
integer_expression
Is a positive whole number. If integer_expression is negative, a null
string is returned."
Hi othellomy,

Your copy of Books Online must be outdated, then. Here's what my version
says:

"integer_expression

"Is a positive integer that specifies how many characters of the
character_expression will be returned. If integer_expression is
negative, an error is returned. integer_expression can be of
type bigint."
>Is there an easier solutoin using left or any other string function
instead of using a case statement?
Also, charindex('/','Hello World Ok') should return NULL instead 0 so
that we can use isnull function.
You can use NULLIF to change the 0 to NULL yourself:

SELECT LEFT ('Hello World Ok',
NULLIF (CHARINDEX('/','Hello World Ok'), 0) - 1);

--
Hugo Kornelis, SQL Server MVP
Nov 3 '06 #2
Thanks :)

Hugo Kornelis wrote:
On 3 Nov 2006 02:45:44 -0800, ot*******@yahoo.com wrote:

(snip)
Microsoft Doc incorrectly says:
"LEFT ( character_expression , integer_expression )
integer_expression
Is a positive whole number. If integer_expression is negative, a null
string is returned."

Hi othellomy,

Your copy of Books Online must be outdated, then. Here's what my version
says:

"integer_expression

"Is a positive integer that specifies how many characters of the
character_expression will be returned. If integer_expression is
negative, an error is returned. integer_expression can be of
type bigint."
Is there an easier solutoin using left or any other string function
instead of using a case statement?
Also, charindex('/','Hello World Ok') should return NULL instead 0 so
that we can use isnull function.

You can use NULLIF to change the 0 to NULL yourself:

SELECT LEFT ('Hello World Ok',
NULLIF (CHARINDEX('/','Hello World Ok'), 0) - 1);

--
Hugo Kornelis, SQL Server MVP
Nov 6 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Agnes | last post: by
18 posts views Thread by Jm | last post: by
3 posts views Thread by OutdoorGuy | last post: by
6 posts views Thread by =?Utf-8?B?R3JlZw==?= | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.