Parsing NameValue Pairs into another Table

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

SQL Server Development (2000)

Parsing NameValue Pairs into another Table

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2001-05-17 : 17:26:26

I have a table which has columns:
ID, NameValuePairs

I'm trying to break these into table 2
with columns:
ID, Name, Value

so if Table 1 has a row like:
1, 'name1=value1;name2=value2'

then Table 2 would get
1, name1, value1
1, name2, value2

Here is what I have so far, I'm sure there is a much better way to do this. Can someone help.

DECLARE @NameValuePairs nvarchar(4000)
DECLARE @Pair nvarchar(4000)
DECLARE @Name nvarchar(4000)
DECLARE @Value nvarchar(4000)

SELECT @NameValuePairs = 'name1=value1;name2=value2;name3=value3'
SELECT @ID = 1

WHILE CharIndex(';', @NameValuePairs) <> 0
BEGIN
SELECT @Pair = Substring(@NameValuePairs, 1, CharIndex(';', @NameValuePairs) - 1)
SELECT @NameValuePairs = Substring(@NameValuePairs, CharIndex(';', @NameValuePairs) + 1, Len(@NameValuePairs))
SELECT @Name = Substring(@Pair, 1, CharIndex('=', @Pair) - 1)
SELECT @Value = Substring(@Pair, CharIndex('=', @Pair) + 1, Len(@Pair))
PRINT @Name
PRINT @Value
INSERT INTO SUBSTITUTION(name,value) VALUES (@Name,@Value)
END
SELECT @Pair = @NameValuePairs
SELECT @NameValuePairs = Substring(@NameValuePairs, CharIndex(';', @NameValuePairs) + 1, Len(@NameValuePairs))
SELECT @Name = Substring(@Pair, 1, CharIndex('=', @Pair) - 1)
SELECT @Value = Substring(@Pair, CharIndex('=', @Pair) + 1, Len(@Pair))
PRINT @Name
PRINT @Value

INSERT INTO SUBSTITUTION(name,value) VALUES (@Name,@Value)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources