I have a data feed that has a :: delimited field with text that is similar to the following:
Categories
Aaaaa::Bbbbbbbbb::Hhhhhhh
Aaaaa::Bbbbbbbbb::Hhhhhhh
Aaaaa::Bbbbbbbbb::Cccccccccc::Cccccccccc
Aaaaa::Bbbbbbbbb::Cccccccccc::Cccccccccc
Aaaaa::Bbbbbbbbb::Cccccccccc:dddd dddd::ddddddddddd
Aaaaa::Bbbbbbbbb::Cccccccccc:dddd dddd::ddddddddddd
Aaaaa::Eeeee E/EEE::Ggggggg::Ffff Ffff$$Aaaaa::Xxxxxxxxx::Yyyyy::Zzz zzzz
Aaaaa::Eeeee E/EEE::Ggggggg::Ffff Ffff$$Aaaaa::Xxxxxxxxx::Yyyyy::Zzz zzz
I need to parse out the text so that each piece (just Aaaaa and Bbbbbbbbb etc.)ends up in its own separate field. Which looks like this.
Field1 Field2 Field3 Field4 Field5
Aaaaa Bbbbbbbbb Hhhhhhh
Aaaaa Bbbbbbbbb Hhhhhhh
Aaaaa Bbbbbbbbb Cccccccccc Cccccccccc
Aaaaa Bbbbbbbbb Cccccccccc Cccccccccc
Aaaaa Bbbbbbbbb Cccccccccc Ddddd ddd Ddddddddddd
Aaaaa Bbbbbbbbb Cccccccccc Ddddd ddd Ddddddddddd
Aaaaa Eeeee E/EEE Ggggggg Ffff Ffff
Aaaaa Eeeee E/EEE Ggggggg Ffff Ffff
Aaaaa Xxxxxxxxx Yyyyy Zzz zzz
Aaaaa Xxxxxxxxx Yyyyy Zzz zzz
I started my code like the following but it has already become unwieldy.
My apologies for the lack of comments.
If Object_ID ('dbo.Temp1')Is Not Null
Drop Table Temp1
Create Table Temp1
(
Field1 varchar(150) Null
, Field6 varchar(150) Null
)
Insert Into Temp1
--(Patindex('%:%',Categories) finds next occurance of ':' in the string
Select Left(Categories,(Patindex('%:%',Categories)) -1) As Field1 --returns first string of characters up to first instance of ':'
, Substring(Categories, Patindex('%:%', Categories) + 2, Len(Categories) - Patindex('%:%', Categories) + 2)As Field6 -- returns remaining part of string
from tmpTireFeed$
Where Categories Not Like '%$$%'
GO
If Object_ID ('dbo.Temp2')Is Not Null
Drop Table Temp2
Create Table Temp2
(
Field1 varchar(150) Null
, Field2 varchar(150) Null
, Field6 varchar(150) Null
)
Insert Into Temp2
Select Field1
,Left(Field6,Case abs((Patindex('%:%',Field6))-1)
When 1 Then Len(field6)
Else abs((Patindex('%:%',Field6))-1)
End
) As Field2
, Substring(Field6, Case abs(Patindex('%:%', Field6) + 2)--Case tests to see if there is no next ':'
When 2 Then 1 --if return is 2 no next instance of ':'
Else abs(Patindex('%:%', Field6) + 2)--Else find next occurance of ':'
End, Len(Field6) - Patindex('%:%', Field6) + 2) As Field6--returns n number of characters left till next ':' occurance
From Temp1
Select *
From Temp2
GO
If Object_ID ('dbo.Temp3')Is Not Null
Drop Table Temp3
Create Table Temp3
(
Field1 varchar(150) Null
, Field2 varchar(150) Null
, Field3 varchar(150) Null
, Field6 varchar(150) Null
)
Declare @return as int
Insert Into Temp3
Select Field1
, Field2
,Left(Field6,Case abs((Patindex('%:%',Field6))-1)
When 1 Then Len(field6)
Else abs((Patindex('%:%',Field6))-1)
End
) As Field3
, Substring(Field6, Case abs(Patindex('%:%', Field6) + 2)
When 2 Then 1
Else abs(Patindex('%:%', Field6) + 2)
End,
Case
--test to see if anything is left to return for field6
When Len(Field6)-(Patindex('%:%', Field6) + 1) > Patindex('%:%', Field6) Then 0
Else Len(Field6) - (Patindex('%:%', Field6) + 1)
End)
From Temp2
Select *
From Temp3
GO
If Object_ID ('dbo.Temp4')Is Not Null
Drop Table Temp4
Create Table Temp4
(
Field1 varchar(150) Null
, Field2 varchar(150) Null
, Field3 varchar(150) Null
, Field4 varchar(150) Null
, Field6 varchar(150) Null
)
Insert Into Temp4
Select Field1
, Field2
, Field3
,Left(Field6,Case abs((Patindex('%:%',Field6))-1)
When 1 Then Len(field6)
Else abs((Patindex('%:%',Field6))-1)
End
) As Field4
, Substring(Field6, Case abs(Patindex('%:%', Field6) + 2)
When 2 Then 1
Else abs(Patindex('%:%', Field6) + 2)
End, --Start
Case
--test to see if anything is left to return for field6
--Number of characters to return
When Len(Field6)<=0 Then 0
Else Len(Field6) - (Patindex('%:%', Field6) + 1)
End) As Field6
From Temp3
Order By Field1
Select *
From Temp4
/***************END OF FIRST BATCH****************************************/
I feel that there has to be a better way and I would appreciate the help.
Thanks in advance and my hats off to you.
Categories
Aaaaa::Bbbbbbbbb::Hhhhhhh
Aaaaa::Bbbbbbbbb::Hhhhhhh
Aaaaa::Bbbbbbbbb::Cccccccccc::Cccccccccc
Aaaaa::Bbbbbbbbb::Cccccccccc::Cccccccccc
Aaaaa::Bbbbbbbbb::Cccccccccc:dddd dddd::ddddddddddd
Aaaaa::Bbbbbbbbb::Cccccccccc:dddd dddd::ddddddddddd
Aaaaa::Eeeee E/EEE::Ggggggg::Ffff Ffff$$Aaaaa::Xxxxxxxxx::Yyyyy::Zzz zzzz
Aaaaa::Eeeee E/EEE::Ggggggg::Ffff Ffff$$Aaaaa::Xxxxxxxxx::Yyyyy::Zzz zzz
I need to parse out the text so that each piece (just Aaaaa and Bbbbbbbbb etc.)ends up in its own separate field. Which looks like this.
Field1 Field2 Field3 Field4 Field5
Aaaaa Bbbbbbbbb Hhhhhhh
Aaaaa Bbbbbbbbb Hhhhhhh
Aaaaa Bbbbbbbbb Cccccccccc Cccccccccc
Aaaaa Bbbbbbbbb Cccccccccc Cccccccccc
Aaaaa Bbbbbbbbb Cccccccccc Ddddd ddd Ddddddddddd
Aaaaa Bbbbbbbbb Cccccccccc Ddddd ddd Ddddddddddd
Aaaaa Eeeee E/EEE Ggggggg Ffff Ffff
Aaaaa Eeeee E/EEE Ggggggg Ffff Ffff
Aaaaa Xxxxxxxxx Yyyyy Zzz zzz
Aaaaa Xxxxxxxxx Yyyyy Zzz zzz
I started my code like the following but it has already become unwieldy.
My apologies for the lack of comments.
If Object_ID ('dbo.Temp1')Is Not Null
Drop Table Temp1
Create Table Temp1
(
Field1 varchar(150) Null
, Field6 varchar(150) Null
)
Insert Into Temp1
--(Patindex('%:%',Categories) finds next occurance of ':' in the string
Select Left(Categories,(Patindex('%:%',Categories)) -1) As Field1 --returns first string of characters up to first instance of ':'
, Substring(Categories, Patindex('%:%', Categories) + 2, Len(Categories) - Patindex('%:%', Categories) + 2)As Field6 -- returns remaining part of string
from tmpTireFeed$
Where Categories Not Like '%$$%'
GO
If Object_ID ('dbo.Temp2')Is Not Null
Drop Table Temp2
Create Table Temp2
(
Field1 varchar(150) Null
, Field2 varchar(150) Null
, Field6 varchar(150) Null
)
Insert Into Temp2
Select Field1
,Left(Field6,Case abs((Patindex('%:%',Field6))-1)
When 1 Then Len(field6)
Else abs((Patindex('%:%',Field6))-1)
End
) As Field2
, Substring(Field6, Case abs(Patindex('%:%', Field6) + 2)--Case tests to see if there is no next ':'
When 2 Then 1 --if return is 2 no next instance of ':'
Else abs(Patindex('%:%', Field6) + 2)--Else find next occurance of ':'
End, Len(Field6) - Patindex('%:%', Field6) + 2) As Field6--returns n number of characters left till next ':' occurance
From Temp1
Select *
From Temp2
GO
If Object_ID ('dbo.Temp3')Is Not Null
Drop Table Temp3
Create Table Temp3
(
Field1 varchar(150) Null
, Field2 varchar(150) Null
, Field3 varchar(150) Null
, Field6 varchar(150) Null
)
Declare @return as int
Insert Into Temp3
Select Field1
, Field2
,Left(Field6,Case abs((Patindex('%:%',Field6))-1)
When 1 Then Len(field6)
Else abs((Patindex('%:%',Field6))-1)
End
) As Field3
, Substring(Field6, Case abs(Patindex('%:%', Field6) + 2)
When 2 Then 1
Else abs(Patindex('%:%', Field6) + 2)
End,
Case
--test to see if anything is left to return for field6
When Len(Field6)-(Patindex('%:%', Field6) + 1) > Patindex('%:%', Field6) Then 0
Else Len(Field6) - (Patindex('%:%', Field6) + 1)
End)
From Temp2
Select *
From Temp3
GO
If Object_ID ('dbo.Temp4')Is Not Null
Drop Table Temp4
Create Table Temp4
(
Field1 varchar(150) Null
, Field2 varchar(150) Null
, Field3 varchar(150) Null
, Field4 varchar(150) Null
, Field6 varchar(150) Null
)
Insert Into Temp4
Select Field1
, Field2
, Field3
,Left(Field6,Case abs((Patindex('%:%',Field6))-1)
When 1 Then Len(field6)
Else abs((Patindex('%:%',Field6))-1)
End
) As Field4
, Substring(Field6, Case abs(Patindex('%:%', Field6) + 2)
When 2 Then 1
Else abs(Patindex('%:%', Field6) + 2)
End, --Start
Case
--test to see if anything is left to return for field6
--Number of characters to return
When Len(Field6)<=0 Then 0
Else Len(Field6) - (Patindex('%:%', Field6) + 1)
End) As Field6
From Temp3
Order By Field1
Select *
From Temp4
/***************END OF FIRST BATCH****************************************/
I feel that there has to be a better way and I would appreciate the help.
Thanks in advance and my hats off to you.