Declare @Temp Table
(Data VarChar(1000),
Region1 VarChar(50),
Region2 VarChar(50),
Region3 VarChar(50),
Region4 VarChar(50),
Region5 VarChar(50),
Region6 VarChar(50))
Insert into @temp(Data) Values('World|USA|New York|New York City|Manhattan|Central Park North')
Insert into @temp(Data) Values('World|USA|New York|Hamptons|Bridgehampton')
Insert into @temp(Data) Values('World|Mexico|Mexican Riviera|Yelapa')
Update @Temp
Set Region1 = Left(Data, Case When CharIndex('|', Data) > 0 Then CharIndex('|', Data) - Sign(CharIndex('|', Data)) Else Len(Data) End),
Data = Right(Data, Len(Data)-Case When CharIndex('|', Data) > 0 Then CharIndex('|', Data) Else Len(data) End)
Update @Temp
Set Region2 = Left(Data, Case When CharIndex('|', Data) > 0 Then CharIndex('|', Data) - Sign(CharIndex('|', Data)) Else Len(Data) End),
Data = Right(Data, Len(Data)-Case When CharIndex('|', Data) > 0 Then CharIndex('|', Data) Else Len(data) End)
Update @Temp
Set Region3 = Left(Data, Case When CharIndex('|', Data) > 0 Then CharIndex('|', Data) - Sign(CharIndex('|', Data)) Else Len(Data) End),
Data = Right(Data, Len(Data)-Case When CharIndex('|', Data) > 0 Then CharIndex('|', Data) Else Len(data) End)
Update @Temp
Set Region4 = Left(Data, Case When CharIndex('|', Data) > 0 Then CharIndex('|', Data) - Sign(CharIndex('|', Data)) Else Len(Data) End),
Data = Right(Data, Len(Data)-Case When CharIndex('|', Data) > 0 Then CharIndex('|', Data) Else Len(data) End)
Update @Temp
Set Region5 = Left(Data, Case When CharIndex('|', Data) > 0 Then CharIndex('|', Data) - Sign(CharIndex('|', Data)) Else Len(Data) End),
Data = Right(Data, Len(Data)-Case When CharIndex('|', Data) > 0 Then CharIndex('|', Data) Else Len(data) End)
Update @Temp
Set Region6 = Left(Data, Case When CharIndex('|', Data) > 0 Then CharIndex('|', Data) - Sign(CharIndex('|', Data)) Else Len(Data) End),
Data = Right(Data, Len(Data)-Case When CharIndex('|', Data) > 0 Then CharIndex('|', Data) Else Len(data) End)
Select * from @Temp