LEFT and string function

[othellomy@yahoo.com]

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_expre ssion , integer_express ion )
integer_express ion
Is a positive whole number. If integer_express ion 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]

Re: LEFT and string function

On 3 Nov 2006 02:45:44 -0800, othellomy@yahoo .com wrote:

(snip)

>Microsoft Doc incorrectly says:
>"LEFT ( character_expre ssion , integer_express ion )
>integer_expres sion
>Is a positive whole number. If integer_express ion 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_expres sion

"Is a positive integer that specifies how many characters of the
character_expre ssion will be returned. If integer_express ion is
negative, an error is returned. integer_express ion 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@yahoo.com]

Re: LEFT and string function

Thanks :)

Hugo Kornelis wrote:

On 3 Nov 2006 02:45:44 -0800, othellomy@yahoo .com wrote:
>
(snip)

Microsoft Doc incorrectly says:
"LEFT ( character_expre ssion , integer_express ion )
integer_express ion
Is a positive whole number. If integer_express ion 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_expres sion
>
"Is a positive integer that specifies how many characters of the
character_expre ssion will be returned. If integer_express ion is
negative, an error is returned. integer_express ion 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