MS SQL Server 2005 UDF only returns first character from a string value

[Steven J Jowett]

I have written a small Scalar UDF that excepts a VarChar value and check to see if the value is NULL. If the value is null an empty string should be returned else the value passed to the UDF should be returned. If the value is NULL an empty string is returned, so that bit works fine, but is I do something like this:-

SELECT dbo.NullToString('Hello world')

The value returned by the UDF is 'H', where I would expect 'Hello world' Here's the UDF code, hopefully someone can enlighten me to my mistake.

CREATE FUNCTION NullToString
(
@value VarChar(255)
)
RETURNS VarChar
AS
BEGIN
DECLARE @ReturnValue VarChar(255)
IF(@value Is NULL)
BEGIN
SET @ReturnValue = ''
END
ELSE
BEGIN
SET @ReturnValue = @value
END

 RETURN @ReturnValue

END
GO

Thanks

Steve Jowett ------------------------- Real programmers don't comment their code. If it was hard to write, it should be hard to read.

[Covean]

Sounds like a ANSI->UTF problem. Try to use NVarChar(255) istead of VarChar(255).

Greetings Covean

[J4amieC]

Im guessing that the default length of a VarChar is 1

RETURNS VarChar

should possibly be

RETURNS VarChar(255)

BTW, you know there is a built-in function for this:

ISNULL(@someVariable,'')

IsNull Function (T-SQL)[^]

[Steven J Jowett]

Thanks for the quick reply, and no I did not know there was a build-in function, so thanks for pointing that out to me. You learn something new everyday Thanks again

Steve Jowett ------------------------- Real programmers don't comment their code. If it was hard to write, it should be hard to read.

[Ashfield]

There is also the coalesce function, which returns the first non-null value from a list

select coalesce(col1val,col2val,'')
from table1

I cannot remember how many values you can have in the list, but its quite a few. I find it useful instead of nesting isnull's

Bob Ashfield Consultants Ltd Proud to be a Code Project MVP

[Steven J Jowett]

Ashfield wrote:

There is also the coalesce function, which returns the first non-null value from a list

That's interesting, I wish I'd known about that one 12 months ago. I shell be making use of that. Thanks Bob

Steve Jowett ------------------------- Real programmers don't comment their code. If it was hard to write, it should be hard to read.

[Ashfield]

Pleased to have been of help

Bob Ashfield Consultants Ltd Proud to be a Code Project MVP