CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	While (Charindex ( @SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring( @RowData,1,Charindex ( @SplitOn,@RowData)-1)))

		Set @RowData = Substring( @RowData,Charindex ( @SplitOn,@RowData)+1,len( @RowData))
	End

	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))
	Return
END

--Declare @numbers table (n int)
--Insert Into @numbers
--Select n = number from dbo.getsequence(0,1000,1)

Declare @numbers table (n int identity(0,1), blah int)
Insert Into @numbers
Select 0 from master.dbo.syscolumns

Declare @myTable table (id int identity(1,1), myVal varchar(1000))
Insert Into @myTable
Select '78770'
Union Select '78762'
Union Select '78764 78758 78758'
Union Select '78779'
Union Select '78769'
Union Select '0'
Union Select '0'

Declare @split table (id int, myVal varchar(1000), posA int, posB int, splitVal varchar(100))

	Insert Into @split
	Select
		id,
		myVal,
		posA,
		posB = min(posB),
		splitVal = rtrim(ltrim(substring(myVal,posA,isnull(min(posB),len(myVal))-posA+1)))
	From
		(		
		Select A.id, A.myVal, posA = B.n, posB = C.n
		From @myTable A
		Inner Join (Select * From @numbers Where n between 0 and (Select max(len(myVal)) From @myTable)) B
		On Substring(A.myVal,B.n,1)=' '
		and len(rtrim(ltrim(A.myVal)))>=B.n
		Left Join (Select * From @numbers Where n between 0 and (Select max(len(myVal)) From @myTable)) C
		On B.n+1<=C.n
		and Substring(A.myVal,C.n,1)=' '
		and len(rtrim(ltrim(A.myVal)))>=C.n
		) Z
	Group by id, myVal, posA

	Select splitVal, count(*) 
	From @split 
	Group By splitVal 
	Order By 2 desc, 1

quote:

---

Originally posted by spirit1

corey... i really think this is a bit of overkill
but hey if it works...

Go with the flow & have fun! Else fight the flow

---

Declare @split table (id int, myVal varchar(1000), posA int, posB int, splitVal varchar(100))

	Insert Into @split
	--group by id, myVal, posA to get the least posB > than posA, and do substring calculation
	Select
		id,
		myVal,
		posA,
		posB = min(posB),
		splitVal = rtrim(ltrim(substring(myVal,posA,isnull(min(posB),len(myVal))-posA+1)))
	From
		(		
		Select A.id, A.myVal, posA = B.n, posB = C.n
		From @myTable A
		--use only the numbers that are less than or equal to the length of the longest set
		Inner Join (Select * From @numbers Where n between 0 and (Select max(len(myVal)) From @myTable)) B
		--join where the delimiter is at the position of n from the numbers table
		On Substring(A.myVal,B.n,1)=' '
		and len(rtrim(ltrim(A.myVal)))>=B.n
		--use only the numbers that are less than or equal to the length of the longest set
		Left Join (Select * From @numbers Where n between 0 and (Select max(len(myVal)) From @myTable)) C
		--join where the delimiter is at the position of n from the numbers table 
		--and greater than the n from the first numbers table
		On B.n+1<=C.n
		and Substring(A.myVal,C.n,1)=' '
		and len(rtrim(ltrim(A.myVal)))>=C.n
		) Z
	Group by id, myVal, posA

	Select splitVal, count(*) 
	From @split 
	Group By splitVal 
	Order By 2 desc, 1