By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,300 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

trigger to parse a string from insert statement

P: n/a
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).

Mar 22 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
<us******@gmail.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.com...
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).

Mar 22 '06 #2

P: n/a
See if this helps:
http://groups.google.com/group/micro...91147f53af5fd9

--
Anith
Mar 22 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.