Splitting Column Value While Keeping Existing Data

• rcr69er

  Ten Centuries

  Points: 1010

  More actions

  June 22, 2015 at 8:25 am

  #318321

  Hi

  Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.

  For example:

  PostCodeLatitudeLongitude

  66000/6610042.6965952.899370

  20251/2027042.1964719.404951

  Would become

  PostCodeLatitudeLongitude

  6600042.6965952.899370

  6610042.6965952.899370

  2025142.1964719.404951

  2027042.1964719.404951

  Any ideas on how this can be done?

  Thanks in advance.

• Y.B.

  SSChampion

  Points: 11649

  More actions

  June 22, 2015 at 9:00 am

  #1807065

  You can easily extract the strings with something like this, provided all of the data follows the same format.

  Cheers,

  DECLARE @postal_latlong NVARCHAR(50) = '66000/66100 42.696595 2.899370'

  SELECT

  @postal_latlong AS Original,

  SUBSTRING(@postal_latlong,0,CHARINDEX('/', @postal_latlong)) AS PostalCode,

  SUBSTRING(@postal_latlong,CHARINDEX('/', @postal_latlong)+1,LEN(@postal_latlong)) AS LongLat

  EDIT: Sorry, I didn't originally notice the second Postal Code. (only had 3 hours sleep...joys of on-call) :doze:

  Obviously both postal codes have the same co-ordinates so you'll need them separated. :crazy:

  This will illustrate how to manipulate some strings. Some good reading here wouldn't hurt: https://msdn.microsoft.com/en-us/library/ms181984.aspx

  SELECT

  --@postal_latlong AS Original,

  SUBSTRING(@postal_latlong,0,CHARINDEX('/', @postal_latlong)) AS PostalCode,

  SUBSTRING(@postal_latlong,CHARINDEX(' ', @postal_latlong)+1,LEN(@postal_latlong)) AS LongLat

  UNION ALL

  SELECT

  SUBSTRING(@postal_latlong,CHARINDEX('/', @postal_latlong)+1,5) AS PostalCode,

  SUBSTRING(@postal_latlong,CHARINDEX(' ', @postal_latlong)+1,LEN(@postal_latlong)) AS LongLat

  EDIT2: Oh Geez, just shoot me now. LOL Also thought they were all in one string. Just check out Luis' post.

  I'll need to add that to my tool belt myself.

  Excuse me while I pull a George Costanza under my desk.

  ---

  SELECT quote FROM brain WHERE original = 1
  0 rows returned

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 22, 2015 at 12:06 pm

  #1807141

  For multiple postcodes (or any other value) stored in a single column, you could use the DelimitedSplit8k which you can find in the following article that explains how it works. http://www.sqlservercentral.com/articles/Tally+Table/72993/

  Example on how would you use it.

  CREATE TABLE SampleData(

  PostCodevarchar( 100),

  Latitudefloat,

  Longitude float)

  INSERT INTO SampleData

  VALUES( '66000/66100', 42.696595, 2.899370),

  ( '20251/20270', 42.196471, 9.404951)

  SELECT sd.PostCode,

  split.Item AS SinglePostCode,

  sd.Latitude,

  sd.Longitude

  FROM SampleData sd

  CROSS APPLY dbo.DelimitedSplit8K( PostCode, '/') split

  GO

  DROP TABLE SampleData

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2