Insert pipe delimited rows into multiple rows - SQL, database, informix, oracle, SQL Server

Insert pipe delimited rows into multiple rows.

	Print
	Email

Submitted on: 10/21/2002 6:09:25 PM
By: Charles Toepfer
Level: Intermediate
User Rating: Unrated
Compatibility:SQL Server 2000, SQL Server 7.0

Users have accessed this article 4222 times.

(About the author)

Take a pipe delimited row, split it out and insert it into multiple rows in a temp table.

Terms of Agreement:
By using this article, you agree to the following terms...
1) You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.

CREATE TABLE tmpPipeSepVals (PipeSepVals VARCHAR(10))

DECLARE @strPipeSepVals varchar(20),
@ThisValue varchar(20),
@EndPoint int

SET @strPipeSepVals = (SELECT [PipeSepValCol] FROM [TABLENAME] WHERE ID = '100')

SET @strPipeSepVals = ltrim(rtrim(convert(varchar,@strPipeSepVals))) + '|'

SET @EndPoint = CHARINDEX('|',@strPipeSepVals)

            WHILE @EndPoint > 0
                        BEGIN
                        SET @ThisValue = SUBSTRING(@strPipeSepVals, 1, @EndPoint - 1)
                        SET @strPipeSepVals = SUBSTRING(@strPipeSepVals, @EndPoint + 1, 100)
                        INSERT INTO [tmpPipeSepVals]([PipeSepVals]) VALUES(@ThisValue)
                        SET @EndPoint = CHARINDEX('|',@strPipeSepVals)
            END

SELECT * FROM [tmpPipeSepVals]

DROP TABLE tmpPipeSepVals

Report Bad Submission

Your Vote!

Other User Comments

10/23/2002 4:44:49 AM:Antonio Rodrigues
Handy code, dude!

3/6/2003 2:25:05 PM:
Hi This type of code I have written as a Stored Procedure. But the problem is SQL can limit the passed length of the string to 2000 chars. Do you have any solution to pass a string more that 2000 chars and split them and store them in the table? Thanks my email is arunk@marketpulse.co.nz

Add Your Feedback!

Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.

NOTICE: The author of this article has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.

For feedback not related to this particular article, please click here.