Solved

Split string and append to other columns

Posted on 2013-12-05
9
329 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 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

19 Experts available now in Live!

Get 1:1 Help Now