LEFT and string function

[othellomy]

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.

[Hugo Kornelis]

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

[othellomy]

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