Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Split string and append to other columns

Posted on 2013-12-05
9
331 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

808 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