Use an Identity Column to Number a Resultset.

This issue's UDF is a contribution from Sloan Holliday. The function, fnc_10_parse_string splits a string up character by character and returns each character as a numbered row. First lets take a look at it and then I'll tell you what I find interesting. Here's the CREATE FUNCTION script.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


-- Drop the function if it exists.
if exists (select * from sysobjects where id 
        = object_id('dbo.fnc_10_parse_string') and xtype = 'TF')
 drop function dbo.fnc_10_parse_string
GO


CREATE  FUNCTION dbo.fnc_10_parse_string(@list varchar(8000))

RETURNS @tablevalues TABLE
               ( itemid int IDENTITY(1,1) 
               , item varchar(8000) )

/* returns each character in @List in a separate row numbered with 
* its position in the string.
* 
* Example:
SELECT * FROM
 dbo.fnc_10_parse_string('abcdefghijklmnopqrstuvwzyz')
****************************************************************/
AS 
BEGIN

declare @pos1 int

select @pos1 = 0

declare @startStringLen int
select @startStringLen = LEN(@list + '*') - 1

--The reason the '*' is added to the end of the string, and then the overall 
--length is subtracted by 1 (to account for the '*') is that the LEN function 
--has a caveat where it will not consider trailing spaces.
-- See BOL or http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-16-udf_Txt_FullLen.htm


DECLARE @P_item varchar(255)

    WHILE (@pos1 < @startStringLen)
                      BEGIN
   
        select @pos1 = @pos1 + 1

        SELECT @p_Item = SUBSTRING(@List,@pos1,1)

        INSERT INTO @tablevalues 
            SELECT Item = @p_Item  
   END

RETURN

END


GO

grant select on dbo.fnc_10_parse_string to Public
go

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

It's pretty simple to try it out:

SELECT * FROM
 dbo.fnc_10_parse_string('ABBBCCDEEADBBDADCCCEDCCCCECEC')
GO


(Results)
itemid      item
----------- -------------
1           A
2           B
3           B
4           B
5           C
6           C
7           D
8           E
9           E
10          A
11          D
12          B
13          B
14          D
15          A
16          D
17          C
18          C
19          C
20          E
21          D
22          C
23          C
24          C
25          C
26          E
27          C
28          E
29          C

The result is a resultset where each character is numbered by its position. The interesting part use the use of the identity column in the result set. This numbers the results in the order in which they are inserted into the RESULT table. I ran into a situation where I need to do something similar last week. Letting the identity column create the numbers saved me from having to have a cursor in my UDF.

Note that the LEN function doesn't include trailing blanks so an asterisk is added to the end of the string when calculating the length. You'll find a UDF that does this in Volume 1 Issue #16.

Thanks to Sloan for this contribution.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick