Split string into Columns based on Special Character

• ksrikanth77

  SSCrazy

  Points: 2232

  More actions

  December 4, 2013 at 4:52 pm

  #283233

  Hi,

  Can anyone help me get the required result in SQL 2012

  Create table DBInfo (Path varchar (500))

  Insert into DBInfo values('/Data Sources')

  Insert into DBInfo values('/Data Sources/SALES')

  Insert into DBInfo values('/PRODUCTION')

  Insert into DBInfo values('/PRODUCTION/SERVICE')

  Insert into DBInfo values('/PRODUCTION/SERVICE/MAINTENANCE')

  Insert into DBInfo values('/PRODUCTION/SERVICE/LOGISTICS')

  My Expected Output

  Column1,Column2,Column3

  Data SourcesNullNull

  Data SourcesSalesNull

  PRODUCTIONNullNull

  PRODUCTIONSERVICENull

  PRODUCTIONSERVICEMAINTENANCE

  PRODUCTIONSERVICELOGISTICS

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  December 5, 2013 at 5:57 am

  #1671021

  This uses Jeff Moden's DelimitedSplit8K function posted at http://www.sqlservercentral.com/articles/Tally+Table/72993/. If you aren't familiar with it yet, take the time to acquainted with it. It's well worth the read.

  select column_a = MAX(case when s.itemnumber = 1 then s.item end),

  column_b = MAX(case when s.itemnumber = 2 then s.item end),

  column_c = MAX(case when s.itemnumber = 3 then s.item end),

  column_d = MAX(case when s.itemnumber = 4 then s.item end)

  from DBInfo

  cross apply DelimitedSplit8K(Path, '/') s

  group by DBInfo.Path;

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions