Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Parsing one column into 3 columns

Posted on 2012-04-02
4
Medium Priority
?
246 Views
Last Modified: 2012-05-31
I have an address column that I need to break into 5 columns.  The data is seperated with comma,s Here is what the data looks like:

General Motors, 30001 Van Dyke Ave, Warren, MI, 48090

and need it split into seperate columns.

Name
Street
City
State
Zip
0
Comment
Question by:knamc
[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
  • 2
  • 2
4 Comments
 
LVL 1

Accepted Solution

by:
mainmind83 earned 1500 total points
ID: 37797913
I prefer do this task with C# to prevent errores but:

http://sqltutorials.blogspot.com.es/2007/09/sql-function-split.html
0
 

Author Comment

by:knamc
ID: 37798762
DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)

SET @String = 'General Motors, 30001 Van Dyke Ave, Warren, MI, 48090'
SET @Delimiter = ','
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SELECT @NextString -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END

This returns:

General Motors

30001 Van Dyke Ave

I need columns:

General Motors         30001 Van Dyke Ave        Warren       MI         48090

This works for a single value but how can I incorporate it into a select statement used to insert into a customer address table?
0
 
LVL 1

Expert Comment

by:mainmind83
ID: 37799431
Create a SQL procedure
0
 

Author Comment

by:knamc
ID: 37803164
I am new to this function and a SQL newbie,  all I am looking for is a little guidance on how to get the output I need and how I can have it load from a table based on the address field I have.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

636 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