November 18, 2004
One of the more useful features of SQL 2000 is the ability to create a user defined function. One great thing you can do, is build a table that is returned to the caller. For example:
CREATE FUNCTION fn_Split (@sText varchar(8000), @sDelim varchar(20) = ‘ ‘)
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
Can then be used as part of an IN clause like this:
WHERE somefield IN (SELECT value FROM fn_split( ‘x,y,z,a,b,c’, ‘,’ ))
That’s really useful. One thing to remember: If you intend to use this function to compare to a field that has a collation defined, then the field returned by this function should use the same collation. Otherwise, you will receive an error about mismatched types.