Dan,

>Has anyone duplicated (or failed to duplicate) common VFP functions as SQL 2000 UDFs? Looks like a good way to easy the transition of VFP views which are littered with IIF(), NVL(), etc.

Others have answered you with performance concerns, but in some cases, there may not be a good way to accomplish what you want without a SQL Server UDF. Here is a semi-substitute for STREXTRACT, which I use to grab a value from a piece of XML stored in a SQL memo field.

NOTE: This works for my purposes, but does not duplicate the STREXTRACT command completely, as it does not handle the 4th and 5th parameters at all. Also, it is limited on purpose in the size of string it can parse and in the return value length, but I didn't have time or patience to figure out how to make it completely generic, since I needed it just for this purpose:

SELECT pk, lastname, 
       dbo.STREXTRACT(subsinfo, '<offer>', '</offer>') AS offer
  FROM subscriptions
 WHERE ...

Works just fine for me. Also note that there MAY be something built into T-SQL to handle this, but I sure couldn't find it and just rolled my own in a few minutes. You can drill into XML somehow in T-SQL with a DOM object, but it may not be any more efficient than this:

CREATE  FUNCTION [dbo].[STREXTRACT] 
    ( @cSearchExpression AS varchar(8000), @cBeginDelim AS varchar(30),
      @cEndDelim AS varchar(30) ) 
RETURNS varchar(100)

AS

BEGIN 
DECLARE @nStart1 AS int
DECLARE @nLength1 AS int
DECLARE @nStart2 As int
DECLARE @nValueStart As int
DECLARE @nValueLength As int

DECLARE @cResult As varchar(30)
SET @cResult = ''

SET @nStart1 = charindex(@cBeginDelim, @cSearchExpression)
SET @nLength1 = LEN(@cBeginDelim)
SET @nStart2 = charindex(@cEndDelim, @cSearchExpression)

IF @nStart1 > 0
  BEGIN
    SET @nValueStart = @nStart1 + @nLength1  
    IF @nStart2 >= @nValueStart
      BEGIN    
        SET @nValueLength = @nStart2 - @nValueStart
        SET @cResult = SUBSTRING(@cSearchExpression, @nValueStart, @nValueLength) 
      END
  END

RETURN @cResult
END 
GO