String Manipulation problem.

[mkal]

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.

 

[fvlo]

You could try something like this:
-----------------------------------------------------------
CREATE FUNCTION GetListItem (
@List VARCHAR(4000) --the @Delimiter-separated list of items
,@Nr INT --the x-th item that should be retrieved
,@Delimiter VARCHAR(10) --the delimiter. Can be more than 1 character!
,@GetNullsAlso CHAR(1) --If 'F', it skips the NULL-values
)
RETURNS VARCHAR(1000)
AS
BEGIN

/*
If @GetNullsAlso = 'F' it skips empty items. So request for 4th item in list 'A,B,,,,C,D' will return 'D'.
*/
DECLARE @ItemNum INT
,@Item VARCHAR(1000)
IF @List IS NULL
BEGIN
RETURN NULL
END
IF @Nr IS NULL
BEGIN
RETURN NULL
END

SET @List = LTRIM(RTRIM(@List)) + @Delimiter -- add the delimiter to the end of the list

IF CHARINDEX (@Delimiter, @List) = 1
BEGIN
SET @List = SUBSTRING(@List,LEN(@Delimiter),LEN(@List)) --remove leading delimiters
END
SET @ItemNum = 0
WHILE @ItemNum < @Nr
BEGIN
IF CHARINDEX(@Delimiter, @List) < 1 --Delimiter not present: result = NULL
BEGIN
RETURN NULL
END
IF @GetNullsAlso = 'F'
BEGIN
IF LEN(SUBSTRING(@List, 1, charindex(@Delimiter, @List)-1 )) > 0
BEGIN
SET @ItemNum = @ItemNum + 1
END
END
ELSE
BEGIN
SET @ItemNum = @ItemNum + 1 --whatever the LEN-value is....
END
SET @Item = SUBSTRING(@List, 1, charindex(@Delimiter, @List)-1 )
SET @List = SUBSTRING(@List, charindex(@Delimiter, @List)+LEN(@Delimiter), LEN(@List) )
END
RETURN @Item
END
go
DECLARE @A VARCHAR(100)

SET @A='AA::BB::CC'
create table Result(c1 varchar(10), c2 varchar(10), c3 varchar(10))

insert Result (c1, c2, c3)
select dbo.GetListItem (@A,1, '::', 'F')
,dbo.GetListItem (@A,2, '::', 'F')
,dbo.GetListItem (@A,3, '::', 'F')


--check it:
select * from Result

--------------------------------------------------------

 

[mkal]

This works great, but I had to work around the limitation of passing a single record at a time. Not your fault it was mine. I inadvertantly displayed the data feed wrong, it should have looked more like this.

ID col1 col2 col3 Categories
1 xxx xxx xxx Aaaaa::Bbbbbbbbb::Hhhhhhh
2 xxx xxx xxx Aaaaa::Bbbbb::Hhhhhhh
3 xxx xxx xxx Aaaaa::Bbbbbb::Cccccc::Cccccccc
4 xxx xxx xxx Aaaa::Bbbbb::Ccccccc::Ccccc:ddddd
5 xxx xxx xxx Aaa::Bbb::Ccc:dd$$Zzz::Eee::Fff::Ggg
.
.
.
5 xxx xxx xxx Aaa::Bbb::Ccc:dd
5 xxx xxx xxx Zzz::Eee::Fff::Ggg


The table can typically have 1000-3000 rows.

So I created a new table with extra columns (Field1, Field2, Field3, Field4, Field5 and bound the function to each one of them and set the param's accordingly. This worked awsomely

Also, note that the last row (5) should end up looking like the last two rows in the above example. I think I have a solution for that as well.

I can't thank you enough!

 

[zhavic]

You can still use the function that 'fvlo' posts:

SET @A='AA::BB::CC$$DDD::UUU::XXX'

SELECT
/* get values from first part of Categories */
1 AS Order_by_column,
dbo.GetListItem( dbo.GetListItem(@A,1, '$$', 'F'), 1, '::', 'F'),
dbo.GetListItem( dbo.GetListItem(@A,1, '$$', 'F'), 2, '::', 'F'),
dbo.GetListItem( dbo.GetListItem(@A,1, '$$', 'F'), 3, '::', 'F'),
...
UNION ALL
SELECT
/* get values from second part of Categories */
2 AS Order_by_column,
dbo.GetListItem( dbo.GetListItem (@A,2, '$$', 'F'), 1, '::', 'F'),
dbo.GetListItem( dbo.GetListItem (@A,2, '$$', 'F'), 2, '::', 'F'),
dbo.GetListItem( dbo.GetListItem (@A,2, '$$', 'F'), 3, '::', 'F'),
...
ORDER BY 1 -- order by first column, that the values from second part of Categories will by at the end of results


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[mkal]

Thanks Zhavic,

This was pretty much what I had in mind but your solution is cleaner than mine.

Hopefully, with time and practice I'll be as good as the rest of you.

again, Thanks
Mike