Split string with delimited chars into a table

/* By Rajesh Jacob(Database Administrator-SAIC)

---this functuion helps us to split the delimited string value into a table which is not directly possible in sql server 2000 and previous versions.

EG:- SELECT * FROM [dbo].[dbo].nom_return_splited_val_fn('text,to,be,splited',',')

*/


create FUNCTION [dbo].nom_return_splited_val_fn(@l_state AS VARCHAR(8000),
@l_delimiter char(1))
RETURNS @state_t TABLE(seq_no numeric(9),
state_split varchar(8000)
)
as
BEGIN
DECLARE @l_state_len numeric(9)
DECLARE @l_state_plit varchar(8000)
DECLARE @l_state_remain varchar(8000)
DECLARE @l_counter numeric(9)
SET @l_state_len=LEN(LTRIM(RTRIM(@l_state)))
SET @l_state_remain=@l_state
set @l_counter=1
WHILE @l_state_len>0
BEGIN
if CHARINDEX(@l_delimiter,@l_state_remain,0)>0
begin
SET @l_state_plit =LEFT(@l_state_remain,CHARINDEX(@l_delimiter,@l_state_remain,0)-1)

INSERT INTO @state_t values(@l_counter,@l_state_plit)

SET @l_state_remain= RIGHT(@l_state_remain,len(@l_state_remain)-CHARINDEX(@l_delimiter,@l_state_remain,0))

SET @l_state_len=LEN(LTRIM(RTRIM(@l_state_remain)))
set @l_counter=@l_counter+1
end
else
begin

INSERT INTO @state_t values(@l_counter,@l_state_remain)
SET @l_state_len=0
end
END
if not EXISTS(select * from @state_t) --if null string is passed an empty record is added
begin
insert into @state_t
values(1,'')
end
RETURN
END

Looks Good. Well the same script I have already uploaded in the form,
Check the for the topic "Comma Seperated values to table form ", Madhivanan any comments ?

Thanks and regards,
Veeresh.


You Store, I Manage