jamestieman
asked on
How do I convert a string of comma separated values into a database table?
I have a user table that looks like this, where AuthorizedDBList is a string that I use in an IN clause of an Access Database.
UserId, AuthorizedDBList
1, 050','051','052',053
What I want is to convert to a SQL Server, Many-to-Many, AuthorizedDatabase table that looks like this.
UserId, DatabaseId
1,050
1,051
1,052
1,053
My question is... how do I get from one to the other?
UserId, AuthorizedDBList
1, 050','051','052',053
What I want is to convert to a SQL Server, Many-to-Many, AuthorizedDatabase table that looks like this.
UserId, DatabaseId
1,050
1,051
1,052
1,053
My question is... how do I get from one to the other?
Is this a one-off conversion? Or, are you doing this on a regular basis?
Where are you executing the code, Access (VBA) or SQL server (TSQL)?
Where are you executing the code, Access (VBA) or SQL server (TSQL)?
You can create a split function, then use a loop control to populate your result table
ALTER FUNCTION [dbo].[Split]
(
@String VARCHAR(8000),
@Delimiter VARCHAR(1)
)
RETURNS @Tokens table (Token VARCHAR(255))
AS
BEGIN
WHILE (CHARINDEX(@Delimiter,@String)>0)
BEGIN
INSERT INTO @Tokens (Token) VALUES
(LTRIM(RTRIM(SUBSTRING(@String,1,CHARINDEX(@Delimiter,@String)-1))))
SET @String = SUBSTRING(@String, CHARINDEX(@Delimiter,@String)+LEN(@Delimiter),LEN(@String))
END
INSERT INTO @Tokens (Token) VALUES (LTRIM(RTRIM(@String)))
RETURN
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
At below you can find excellent way to generate a table from comma separated string..
http://www.sqlservercentra l.com/arti cles/Strin g+Manipula tion/72540 /
Let me know if you have any doubts..
http://www.sqlservercentra
Let me know if you have any doubts..
I think you should have a look at http://sommarskog.se/array s-in-sql-2 005.html
Erland is a MVP, and his entire homepage is worth spending quite a few hours on reading and understanding.
The link is pointing at a split function, which is implemented in many different ways, and compared to each other for speed, features and robustness.
HIH
Best regards,
Henrik Staun Poulsen, Stovi Software
Erland is a MVP, and his entire homepage is worth spending quite a few hours on reading and understanding.
The link is pointing at a split function, which is implemented in many different ways, and compared to each other for speed, features and robustness.
HIH
Best regards,
Henrik Staun Poulsen, Stovi Software
ASKER
I appreciate everyone's advice, but ewangoya gave me a cut and paste solution that worked the first time.
Thanks to all
Jamie
Thanks to all
Jamie
Then pull it into sql which should give each value its own column as you've removed the quotes. from there do a PIVOT statement (http://msdn.microsoft.com/en-us/library/ms177410.aspx) and your done
Thanks
Dave