I will be more happy to provider you the DDl and the Data,

I ahve working on this since yesterday with no results.I will appreciate if you can help.

My table has around 2000k rows.

Here is the DDl for the :

USe [Tempdb]

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..customer_address','U') IS NOT NULL

DROP TABLE customer_address

--===== Create the test table with

CREATE TABLE 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 'AALEMANSOUR, SIAMAK: 858256221','18141 BEACH BLVD STE 130* HUNTINGTN BCH* CALIFORNIA*92648*(02/02/06 - 03/11/09)' UNION ALL

SELECT 'ABAD, ERWIN: 256585455 ','1475 BANNISTER ST* YORK* PENNSYLVANIA*17404*(01/11/06 - 06/25/08)' UNION ALL

SELECT 'ABBOTT, ROGER: 256123456','650 S MAIN ST* RIVER FALLS* WISCONSIN*54022*(01/23/06 - 05/28/08)' UNION ALL

SELECT 'AGUTO, FELIX: 550839635','15293 AMBERLY DR* TAMPA* FLORIDA*33647*(03/06/06 - 09/26/07)' UNION ALL

SELECT 'AGEE, DEEP: 21541215','19 E THIRD ST* MAYSVILLE* KENTUCKY*41056*(01/03/06 - 06/10/08)' UNION ALL

SELECT 'AGOSTONI, DAVID: 515451112','11005 S PARKER RD* PARKER* COLORADO*80134*(01/31/06 - 11/09/07)' UNION ALL

SELECT 'AGUIRRE, RAMON: 23154512','159 S MAIN AVE* SIOUX CENTER* IOWA*51250*(01/25/06 - 09/01/06)' UNION ALL

SELECT 'AGUTO, FELIX: 254612542','650 S MAIN ST* RIVER FALLS* WISCONSIN*54022*(01/23/06 - 05/28/08)'

OUTPUT I Expect is

Provider_complete_address has to be splitted into address,city and state.

This is the way i want my output to look.

Customer_key, address, City, State

Thanks in advance It will be great help.

thanks