USE [SandBox]
GO
/****** Object:  UserDefinedFunction [dbo].[DelimitedSplit2]    Script Date: 05/07/2009 10:03:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[DelimitedSplit2] (
    @pString varchar(max),
    @pDelimiter varchar(2)
)
returns table
as
return
with
a1 as (select 1 as N union all
       select 1 union all
       select 1 union all
       select 1 union all
       select 1 union all
       select 1 union all
       select 1 union all
       select 1 union all
       select 1 union all
       select 1),
a2 as (select
            1 as N
       from
            a1 as a
            cross join a1 as b),
a3 as (select
            1 as N
       from
            a2 as a
            cross join a2 as b),
a4 as (select
            1 as N
       from
            a3 as a
            cross join a2 as b),
Tally as (select top (DATALENGTH(@pString))
            row_number() over (order by N) as N
          from
            a4),
ItemSplit(
    ItemOrder,
    Item
) as (
SELECT
    N,
    SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + DATALENGTH(@pDelimiter),CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + DATALENGTH(@pDelimiter)) - N - DATALENGTH(@pDelimiter))
FROM
    Tally
WHERE
    N < DATALENGTH(@pDelimiter + @pString + @pDelimiter)
    AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,DATALENGTH(@pDelimiter)) = @pDelimiter --Notice how we find the delimiter
)
select
    row_number() over (order by ItemOrder) as ItemID,
    Item
from
    ItemSplit
GO

declare @Address varchar(256);
set @Address = '5605 PRINCETON AVE STE B* COLUMBUS* GEORGIA*31904*(01/11/06 - 03/26/09)'

select * from dbo.DelimitedSplit2(@Address, '*')

CREATE FUNCTION [dbo].[fSplit]
(
	@List VARCHAR(6000),
	@SplitOn VARCHAR(5)
)  
RETURNS @RtnValue TABLE
(
		
	ID INT identity(1,1),
	Value VARCHAR(100)
) 
AS  
BEGIN 
WHILE (Charindex(@SplitOn,@List)>0)
	BEGIN  
		INSERT INTO 
			@RtnValue (value)
		SELECT 
		    	Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

		SET @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
	END     

	INSERT INTO 
		@RtnValue (Value)
	    SELECT 
		Value = ltrim(rtrim(@List))
	
	    RETURN
END

SELECT dbo.fSplit(MyAddressField,'*')
FROM MyTable

CREATE TABLE dbo.customer_address 
(
Customer_key NVARCHAR(100),
Provider_complete_Address NVARCHAR(150)
)

--===== Insert the test data into the test table
INSERT INTO customer_address 
(Customer_key,Provider_complete_Address)
SELECT N'AALEMANSOUR, SIAMAK: 858256221','18141 BEACH BLVD STE 130* HUNTINGTN BCH* CALIFORNIA*92648*(02/02/06 - 03/11/09)' UNION ALL
SELECT N'ABAD, ERWIN: 256585455 ','1475 BANNISTER ST* YORK* PENNSYLVANIA*17404*(01/11/06 - 06/25/08)' UNION ALL
SELECT N'ABBOTT, ROGER: 256123456','650 S MAIN ST* RIVER FALLS* WISCONSIN*54022*(01/23/06 - 05/28/08)' UNION ALL
SELECT N'AGUTO, FELIX: 550839635','15293 AMBERLY DR* TAMPA* FLORIDA*33647*(03/06/06 - 09/26/07)' UNION ALL
SELECT N'AGEE, DEEP: 21541215','19 E THIRD ST* MAYSVILLE* KENTUCKY*41056*(01/03/06 - 06/10/08)' UNION ALL
SELECT N'AGOSTONI, DAVID: 515451112','11005 S PARKER RD* PARKER* COLORADO*80134*(01/31/06 - 11/09/07)' UNION ALL
SELECT N'AGUIRRE, RAMON: 23154512','159 S MAIN AVE* SIOUX CENTER* IOWA*51250*(01/25/06 - 09/01/06)' UNION ALL
SELECT N'AGUTO, FELIX: 254612542','650 S MAIN ST* RIVER FALLS* WISCONSIN*54022*(01/23/06 - 05/28/08)'

select
    Customer_key,
    max(case ItemID when 1 then ltrim(rtrim(Item)) else null end) as Addr,
    max(case ItemID when 2 then ltrim(rtrim(Item)) else null end) as City,
    max(case ItemID when 3 then ltrim(rtrim(Item)) else null end) as [State]
from
    dbo.customer_address
    cross apply dbo.DelimitedSplit2(Provider_complete_Address,'*')
group by
    Customer_key;

drop table dbo.customer_address;