I have a user defined function in SQL Server 2000 that splits a Name field into First, Last, Middle names. Originally I utilized the CHARINDEX function to find the spaces in the string. Since I then found out that function is by definition Non-deterministic that didn't work for me, so I re-wrote the function a bit differently.

I am using the function as a field's formula in my table structure. I need the ability to create an index on that field so need the function to be deterministic. Apparently there is still something about how I designed the function that causes it to be non-deterministic. The function code is below; if anyone can help point me in the right direction it'd be greatly appreciated.

ALTER function fSplitName_test
(
@name varchar(100),
@fname int)
returns varchar(55)
as

begin
declare @Fname_position int, @Lname_position int, @Mname_position int, @SplitName varchar(55), @iint int, @iLen int,
@sChar char(1), @sName varchar(55)


set @name = ltrim(replace(@name, ' JR', ''))
set @sName = @name
set @ilen = len(rtrim(@name))
set @Fname_position = 0
set @Lname_position = 0
set @iint = 0

while @iint <> @ilen
begin
set @schar = left(@sName, 1)
begin
if @schar = ' '
begin
if @Fname_position = 0
set @Fname_position = @iint
else
set @Lname_position = @iint
end
set @iint = @iint + 1
set @sName = right(@sName, @ilen - @iint)
end
end
if @fname = 1
begin
if @fname_position <> 0
set @SplitName = left(@name, @Fname_position)
else
set @splitname = 'unkn'
end
else
begin
if @fname = 2
begin
if @lname_position <> 0
set @SplitName = right(@name, @ilen - @Lname_position-1)
else
set @SplitName = right(@name, @ilen - @fname_position)
end
else
begin
if @fname_position + @lname_position <> @ilen and @lname_position <> 0
begin
set @SplitName = left(@name, @lname_position)
set @splitname = right(@splitname,len(@splitname) - @fname_position - 1)
end
else
set @splitname = 'unkn'
end
end
return @SplitName
end

You might get more response if you post this under the "Microsoft SQL Server" forum. This one's for generic SQL questions - I for one know nothing about SQL Server.

what if a person has a lastname like this "DELA CRUZ"
can you post an example of the name parameter ex: "Michael Angelo Rodriguez" or "Michael Angelo Dela Cruz" coz as you can see on the 2 ex. it is hard to detemine the first name on the first example and last name on the latter the name should have a middle initial or comma perhaps.

This is probably completely wrong, but I wouldn't put it past SQL Server to think that you were trying to modify the parameters. That would, of course, make the function non-deterministic. Try copying them to local variables.

Otherwise, the way to debug a problem like this is to break your function into small functions and verify that the small functions are all deterministic.