Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Split string and append to other columns

Posted on 2013-12-05
9
Medium Priority
?
336 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
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 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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

877 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