Here is the code I threw together:

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;