Split field into multiple rows

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 2000 Forums

Transact-SQL (2000)

Split field into multiple rows

Author

Topic

crackerbox
Starting Member

21 Posts

Posted - 2007-11-09 : 11:49:05

I have a message field that is being used to collect some codes.

Example:

ABC/DEF/GHI/JKLM/NO

I need a script to split this entry into 5 rows in a table.
The number of characters between the slashes varies. It's also not a one time thing so data is being added to the message field all the time so I was thinking about a view.

Does anyone have a suggestion.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-09 : 11:58:33

search these forums for a split function...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-11-10 : 02:26:52

[code]
CREATE FUNCTION dbo.fnSplit(
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE
(
Id int identity(1,1),
item VARCHAR(8000)
)

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END

IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
[/code]

I get this code somewhere on this forum. I used this on my production server. I dig into it and paste it here. Just look for the SPLIT search string and see others approach.

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-10 : 03:52:06

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2007-11-10 : 05:40:02

"WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0"

Note that this looping approach is very slow if the delimited list has more than just a few items

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources