Insert each item in a comma delimited textfield as

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert each item in a comma delimited textfield as

Author

Topic

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-02-10 : 11:39:13

I would like to Insert each item in a comma delimited textfield as its own record?

Basically there's just a textfield where users will input a comma delimited list. I need to insert each item as it's own record along with another field. So the form is like:

<input type="text" name="keywords"> this will be a comma delimited list
<input type="hidden" name="someid" value="#URL.someid#">
This will stay the same

Needs to go in as:

foreignkeyid Keyword
1. 235 keyword1
2. 235 keyword2
3. 235 keyword3

Instead of:
Foreignkeyid. Keyword
1. 235 keyword1, keyword2, keyword3

I'm using SQL server.

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-10 : 12:47:28

Use this function or something similar. There are quite a few in this forum.


CREATE FUNCTION [dbo].[udf_SplitChar]
(
	@CSVList varchar(max), @Delimiter char(1) = ','
)
RETURNS 
@ParsedList table
(
	ListValue varchar(500)
)
AS
BEGIN
	DECLARE @ListValue varchar(500), @Pos int

	SET @CSVList = LTRIM(RTRIM(@CSVList))+ @Delimiter
	SET @Pos = CHARINDEX(@Delimiter, @CSVList, 1)

	IF REPLACE(@CSVList, @Delimiter, '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @ListValue = LTRIM(RTRIM(LEFT(@CSVList, @Pos - 1)))
			IF @ListValue <> ''
			BEGIN
				INSERT INTO @ParsedList (ListValue) 
				VALUES (@ListValue) --Use Appropriate conversion
			END
			SET @CSVList = RIGHT(@CSVList, LEN(@CSVList) - @Pos)
			SET @Pos = CHARINDEX(@Delimiter, @CSVList, 1)

		END
	END	
	RETURN
END

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-10 : 23:58:47

try this
declare @str table(id int,data varchar(128))
insert into @str select 235,'keyword1,keyword2,keyword3'

SELECT
id,replace(SUBSTRING(data,charindex(',',data,v.number),abs(charindex(',',data,charindex(',',data,v.number)+1)-charindex(',',data,v.number))),',','')as value
FROM @str AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
AND v.number > 0
AND v.number <= len(data)
WHERE substring(',' + data, v.number, 1) = ','

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-11 : 00:10:50

declare @str table(id int,data varchar(128))
insert into @str
select 235,'keyword1,keyword2,keyword3'

declare @str1 VARCHAR(32)
select @str1 = data from @str
select @str1 = @str1+','

;WITH csvtbl(i, j,id)
AS
(
SELECT 0, j = CHARINDEX(',', @Str1),id from @str
UNION ALL
SELECT CAST(j + 1 AS INT), j = CHARINDEX(',', @Str1, j + 1) ,Id
FROM csvtbl
WHERE CHARINDEX(',', @Str1, j + 1) <> 0
)

SELECT c.id,SUBSTRING(@Str1, i, j-i) as data
FROM csvtbl c
inner join @str s on s.id = c.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources