?
Solved

How do I convert a string of comma separated values into a database table?

Posted on 2011-09-08
7
Medium Priority
?
218 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:jamestieman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36505807
if this is a one off I'd suggest exporting to a text editor and removing the quotes round the string.

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
0
 
LVL 9

Expert Comment

by:borki
ID: 36505814
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)?
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 36507068

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

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 36507151
you can use it in this manner
declare @test table(id int, value varchar(512))
declare @result table(id int, value varchar(255))

insert into @test 
select 1, '12,34,45,56,76' 
union all
select 2, '98,87,5'

declare @id integer, @values varchar(10)

select top 1 @id = ID from @test order by id

while exists(select 1 from @test where id >= @id)
begin
  select top 1 @id = Id, @values = value
  from @test
  where id >= @id
  order by id asc
  
  insert into @result
  select @id, Token
  from dbo.Split(@values, ',') A
  
  set @id = @id + 1
end

select * from @result

Open in new window

0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 36507880
At below you can find excellent way to generate a table from comma separated string..

http://www.sqlservercentral.com/articles/String+Manipulation/72540/

Let me know if  you have any doubts..
0
 
LVL 3

Expert Comment

by:hspoulsen
ID: 36509112
I think you should have a look at http://sommarskog.se/arrays-in-sql-2005.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

0
 

Author Closing Comment

by:jamestieman
ID: 36510668
I appreciate everyone's advice, but ewangoya gave me a cut and paste solution that worked the first time.  
Thanks to all
Jamie
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question