sql server (alternate): trigger to parse a string from insert statement -- sql server (alternate)

trigger to parse a string from insert statement

uspensky NO[at]SPAM gmail.com
3/21/2006 4:37:02 PM

sql server (alternate): I have two tables:

tb_news--
-story_id
-productlist

tb_lookup--
-story_id
-product

when an insert command is run on tb_news, productlist field is
populated with a value such as 'abc, def, de'

when this happens, i need tb_lookup to be populated with seperate
records for each product in productlist and the story_id from tb_news.

Example:
INSERT INTO tb_news (story_id, product_list)
VALUES (12345, 'abc, def, de')

Results:
tb_news--
12345, 'abc, def, de'

tb_lookup--
12345, 'abc'
12345, 'def'
12345, 'de'

Ideally, I would like this to use recursion and give me tha ability to
change the delimiter at any time (might not always be a comma). some
products may have a period in them. number of products is unknown and
might be 0 (field may be empty or NULL).

Re: trigger to parse a string from insert statement

Uri Dimant
3/22/2006 12:00:00 AM

Hi
I'd prefer doing such things on the client side
create table w
(
id int,
t varchar(50)
)

insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w

create function dbo.fn_my ( @id int)
returns varchar(100)
as
begin
declare @w varchar(100)
set @w=''
select @w=@w+t+',' from w where id=@id
return @w
end

select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd

drop function dbo.fn_my

[quoted text, click to view]