Solved

Split string and append to other columns

Posted on 2013-12-05
9
326 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now