seperate column into multiple based on symbol - parsename

• joanna.seldon

  Ten Centuries

  Points: 1188

  More actions

  April 20, 2017 at 7:37 am

  #329577

  Hi

  need little help

  I have a column in a system called CateringStaff

  data looks like this in column

  1919:BAKER:01:CAKES

  Used sql code that works really well

  Select 
   
  CateringStaff

  ,parsename(replace(CateringStaff,':','.'),5) as [Column 1]
  
  ,parsename(replace(CateringStaff,':','.'),4) as [Column 2]

  ,parsename(replace(CateringStaff,':','.'),3) as [Column 3]

  ,parsename(replace(CateringStaff,':','.'),2) as [Column 4]

  ,parsename(replace(CateringStaff,':','.'),1) as [Column 5]

  from Table

  Issue I have is when odd data has been entered into the column as

  2015:COOK:08:VEGETABLES:

  Because of the extra : symbol at the end I have issues. it returns all nulls for the row of data, or any row that is the same with the extra : symbol at the end. how can the code above be amended to solve this please?

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  April 20, 2017 at 8:05 am

  #1939092

  joanna.seldon - Thursday, April 20, 2017 7:37 AM

  Hi

  need little help

  I have a column in a system called CateringStaff

  data looks like this in column

  1919:BAKER:01:CAKES

  Used sql code that works really well

  Select 
   
  CateringStaff
  ,parsename(replace(CateringStaff,':','.'),4) as [Column 1]

  ,parsename(replace(CateringStaff,':','.'),3) as [Column 2]

  ,parsename(replace(CateringStaff,':','.'),2) as [Column 3]

  ,parsename(replace(CateringStaff,':','.'),1) as [Column 4]

  from Table

  Issue I have is when odd data has been entered into the column as

  2015:COOK:08:VEGETABLES:

  Because of the extra : symbol at the end I have issues. it returns null for the row of data, or any row that is the same with the extra : symbol at the end. how can the code above be amended to solve this please?

  This should get you going
  😎

  DECLARE @CateringStaff VARCHAR(1000) = '2015:COOK:08:VEGETABLES:';

  SELECT
    CASE
        WHEN LEN(@CateringStaff) - LEN(REPLACE(@CateringStaff,':','')) > 3 THEN SUBSTRING(@CateringStaff,1,LEN(@CateringStaff) - 1)
        ELSE @CateringStaff
    END AS CateringStaff
;


• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  April 20, 2017 at 8:32 am

  #1939095

  ParseName function has several limitations. To reduce them, here's an option using DelimitedSplit8K which is explained in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

  CREATE TABLE SampleData(
  CateringStaff varchar(8000)
);

  INSERT INTO SampleData
VALUES('2015:COOK:08:VEGETABLES: ')

  SELECT d.CateringStaff,
   MAX( CASE WHEN s.ItemNumber = 1 THEN s.Item END) AS [Column 1],
   MAX( CASE WHEN s.ItemNumber = 2 THEN s.Item END) AS [Column 2],
   MAX( CASE WHEN s.ItemNumber = 3 THEN s.Item END) AS [Column 3],
   MAX( CASE WHEN s.ItemNumber = 4 THEN s.Item END) AS [Column 4],
   MAX( CASE WHEN s.ItemNumber = 5 THEN s.Item END) AS [Column 5]
FROM SampleData d
CROSS APPLY dbo.DelimitedSplit8K(d.CateringStaff, ':') s
GROUP BY d.CateringStaff

  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

• joanna.seldon

  Ten Centuries

  Points: 1188

  More actions

  May 5, 2017 at 2:22 am

  #1941206

  Hi

  worked ! thanks team