Solved

Split string and append to other columns

Posted on 2013-12-05
9
332 Views
Last Modified: 2013-12-06
Dear Experts,

I have these sets of string data that are right now stored in single DB column but I wanted to break them and distribute to their respective columns as follows:

Kansas City, KS 66016  > City: Kansas City, State: KS, Zip: 66016
Chicago, IL 60636 > City: Chicago, State: IL, Zip: 60636

MyCompany, Atlanta, GA 30315 > Client: MyCompany, City: Atlanta, State: GA, Zip: 30315
YourCompany,Chicago, IL 60636 > Client: YourCompany, City: Chicago, State: IL, Zip: 60636

Your help will be much appreciated,

All the best,
0
Comment
Question by:JimiJ13
  • 5
  • 4
9 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39700102
The following two queries do what you ask, although they are dependent on all the spaces being exactly the same. It could be made more generic but I'm sure you can manage that once you see how this works.

select 'City: ' + substring(Data, 1, FirstComma) + ' State: ' + substring(Data, FirstComma+2, FirstSpace-FirstComma-2) + ', Zip: ' + substring(Data, FirstSpace+1, len(Data))
  , Data, FirstComma, FirstSpace
from (
  select Data
    , charindex(',', data, 1) FirstComma
    , charindex(' ', data, charindex(',',data,1)+2) FirstSpace
  from (
    select 'Kansas City, KS 66016' Data union all select 'Chicago, IL 60636'
  ) x
) y

select 'Company: ' + substring(Data, 1, FirstComma) + ' City: ' + substring(Data, FirstComma+2, SecondComma-FirstComma-2) + ', State: ' + substring(Data, SecondComma+2, FirstSpace-SecondComma-2) + ', Zip: ' + substring(Data, FirstSpace+1, len(Data))
  , Data, FirstComma, SecondComma, FirstSpace--, Temp
from (
  select Data
    , charindex(',', data, 1) FirstComma
    , charindex(',', data, charindex(',', data,1)+1) SecondComma
    --, charindex(',', data, charindex(',', data, 1)+1)+2 Temp
    , charindex(' ', data, charindex(',', data, charindex(',', data, 1)+1)+2) FirstSpace
  from (
    select 'MyCompany, Atlanta, GA 30315' Data union all select 'YourCompany, Chicago, IL 60636'
  ) x
) y

Open in new window

0
 

Author Comment

by:JimiJ13
ID: 39700181
dale_burrell,

Looks great!

How can you manage to insert them to to another columns like:

From this: Table1.Location = 'Kansas City, KS 66016'

To another columns as follows:

Table1.City = 'Kansas City'
Table1.State= 'KS'
Table1.Zip ='66016'

Thanks,
0
 

Author Comment

by:JimiJ13
ID: 39700184
BTW, the other requirements is having another column:


Table1.Client = 'MyCompany'



Thanks,
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39700193
Something like the following should do the job... I've leave the second example as an exercise for you.

declare @MyTableValues table (id varchar(38), City varchar(1024, [State] varchar(1024), Zip varchar(1024))

insert into @MyTableValues (id, City, [State], Zip)
  select id, substring(Data, 1, FirstComma), substring(Data, FirstComma+2, FirstSpace-FirstComma-2), substring(Data, FirstSpace+1, len(Data))
  --select 'City: ' + substring(Data, 1, FirstComma) + ' State: ' + substring(Data, FirstComma+2, FirstSpace-FirstComma-2) + ', Zip: ' + substring(Data, FirstSpace+1, len(Data))
  --  , Data, FirstComma, FirstSpace
  from (
    select id, Data
      , charindex(',', data, 1) FirstComma
      , charindex(' ', data, charindex(',',data,1)+2) FirstSpace
    from (
      -- select 'Kansas City, KS 66016' Data union all select 'Chicago, IL 60636'
      select id, MyOriginalField from MyTable
    ) x
  ) y

update MyTable set
  City = New.City
  , [State] = New.[State]
  , Zip = New.Zip
from MyTable, @MyTableValues New
where MyTable.id = New.id

Open in new window

0
 

Author Comment

by:JimiJ13
ID: 39700211
Thanks!

I have created the MyTable :

CREATE TABLE [dbo].[MyTable](
      [id] [varchar](38) NULL,
      [City] [varchar](50) NULL,
      [State] [varchar](50) NULL,
      [Zip] [varchar](50) NULL,
      [MyOriginalField] [varchar](150) NULL,
      [Data] [varchar](150) NULL
) ON [PRIMARY]


And this is the error I got:
Msg 207, Level 16, State 1, Line 8
Invalid column name 'Data'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'data'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'data'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'data'.

Any idea?
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39700218
You don't need Data & MyOriginalField - thats the same field, I just changed name when I re-wrote to use a real table. So choose which one you want, and then change all references in the query (or if you use MyOriginalField you can alias it to date e,g, MyOriginalField  as Data).
0
 

Author Comment

by:JimiJ13
ID: 39700463
Thanks! I have only one more thing remaining:   To remove the comma (,) on the City value.

id      City      State      Zip      data

31ww      Kansas City,      KS      66016      Kansas City, KS 66016
3222      Chicago,      IL      60636      Chicago, IL 60636


All the best!
0
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 500 total points
ID: 39700478
If you look up the substring function (http://technet.microsoft.com/en-us/library/ms187748.aspx) you'll see that it looks like this "SUBSTRING ( expression ,start , length )".

So if you change the current length setting by add "-1" at the end it will remove the last character, which I believe it the comma.
0
 

Author Closing Comment

by:JimiJ13
ID: 39700855
Excellent!
0

Featured Post

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!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

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