Insert new for every Charater in varchar param

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 new for every Charater in varchar param

Author

Topic

solent
Starting Member

33 Posts

Posted - 2007-09-12 : 07:04:44

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-12 : 07:34:17

Start here.
http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

PS: If anyone is interested in a high paying SQL position in CT please send me your resume to ValterBorges@msn.com

solent
Starting Member

33 Posts

Posted - 2007-09-12 : 07:47:42

thank you

ranganath
Posting Yak Master

209 Posts

Posted - 2007-09-12 : 08:16:41

Hi,

Try This

DECLARE @Data NVARCHAR(MAX), @delimiter NVARCHAR(5)
Declare @Table Table (Id int identity(1,1), Names varchar(100))

SELECT @data = 'Lakshmi|Subrahamanyam|Ranga|sai|Kumar|Keerthi|chetu',
@delimiter = '|'

DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);

Insert Into @Table
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)

select * From @Table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-12 : 08:31:23

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

solent
Starting Member

33 Posts

Posted - 2007-09-12 : 10:10:06

good reads thanks guys

solent
Starting Member

33 Posts

Posted - 2007-09-13 : 02:04:49

quote:
Originally posted by ranganath

Hi,

Try This

DECLARE @Data NVARCHAR(MAX), @delimiter NVARCHAR(5)
Declare @Table Table (Id int identity(1,1), Names varchar(100))

SELECT @data = 'Lakshmi|Subrahamanyam|Ranga|sai|Kumar|Keerthi|chetu',
@delimiter = '|'

DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);

Insert Into @Table
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)

select * From @Table

Thank you ranganath. That worked perfectly well for 2 tables.

solent
Starting Member

33 Posts

Posted - 2007-09-13 : 04:31:34

quote:
Originally posted by ranganath

Hi,

Try This

DECLARE @Data NVARCHAR(MAX), @delimiter NVARCHAR(5)
Declare @Table Table (Id int identity(1,1), Names varchar(100))

SELECT @data = 'Lakshmi|Subrahamanyam|Ranga|sai|Kumar|Keerthi|chetu',
@delimiter = '|'

DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);

Insert Into @Table
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)

select * From @Table

i have another question based on this example.
I have one aditional variable
DECLARE @RoleData NVARCHAR(MAX)
SELECT @RoleData = 'Role1|Role2|Role3|Role4|Role5|Role6|Role7',
@delimiter = '|'

How can i include the @RoleData in the SELECT @textXML so that for every @Data i can have a @RoleData and then insert it as another column in the @Table ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 04:45:59

E 12°55'05.25"
N 56°04'39.16"

solent
Starting Member

33 Posts

Posted - 2007-09-13 : 05:17:20

quote:
Originally posted by Peso

DECLARE @UserData NVARCHAR(MAX),
@RoleData NVARCHAR(MAX)

SELECT @UserData = 'Lakshmi|Subrahamanyam|Ranga|sai|Kumar|Keerthi|chetu',
@RoleData = 'Role1|Role2|Role3|Role4|Role5|Role6|Role7'

SELECT u.Data,
r.Data
FROM fnParseList('|', @UserData) AS u
INNER JOIN fnParseList('|', @RoleData) AS r ON r.RowID = u.RowID
fnParseList found here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033
(scroll down to second version of the function)

E 12°55'05.25"
N 56°04'39.16"

briliant

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources