Solved

Parsing one column into 3 columns

Posted on 2012-04-02
4
242 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
  • 2
  • 2
4 Comments
 
LVL 1

Accepted Solution

by:
mainmind83 earned 500 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 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