charindex in user-defined function

Hi,
I'm trying to use the CHARINDEX built-in function in my user-defined
function, but it doesn't seem to work.
Right now I got down to only write a test-function that doesn't do anything
else but call charindex:
CREATE FUNCTION X(@.p_str1 VARCHAR, @.p_str2 VARCHAR)
RETURNS INT AS
BEGIN
RETURN CHARINDEX(@.p_str1, @.p_str2);
END
Now, when I do a simple select like this:
SELECT NAME, dbo.X('Smith', NAME) AS Expr1, CHARINDEX('Smith', NAME) AS
Expr2 FROM PERSONS
The second column will be 0 everywhere, so rows like this occur:
John Smith, 0, 6
I've read that non-deterministic functions are not allowed to be used in
user-defined functions, and I suspect that this is what causes this.
In this case:
1. Why can't non-deterministic functions be used?
2. Shouldn't there be some error thrown in dbo.X() instead of it simply
returning 0?
3. Is there some similar deterministic function that can be used? I really
need this function for doing some basic parsing, I'm don't want write my own
in vain.
4. How on Earth can such a simple function be non-deterministic?
Thx
The problem with your function is that you didn't define the size of the
input parameters, therefore your strings get truncated to VARCHAR(1). For
example, change it to:
CREATE FUNCTION X(@.p_str1 VARCHAR(30), @.p_str2 VARCHAR(30))
...
Some non-deterministic functions can be used in a UDF but others aren't
permitted, mainly I think because of the difficulties that could result from
using those functions in queries or in conjunction other features that might
reference the UDF.
Your system date is incorrect. You may not get many replies because people
who sort posts by date won't see your post as a recent one.
David Portas
SQL Server MVP