set nocount on

create table #customer
	(custID int, custName varchar(15), custAddress varchar(40), city varchar(15), state varchar(2))
go
insert #customer
select 1, 'CustomerABC', '4 Main Road, Irvine, CA',     	'Irvine',     'CA' union all
select 2, 'CustomerDEF', '123 Boundary Street, New York, NY',     'New York',     'NY' union all
select 3, 'CustomerXYZ', '8 25th Avenue, San Francisco, CA',     'San Francisco',     'CA'
go
alter table #customer add address1 varchar(30)
go

update #customer set
	address1 = substring(replace(replace(custAddress, ', ' + city, ''), ', ' + state, ''),1,30)
from	#customer

select custid
	,custname
	,address1
	,city
	,state
from #customer

drop table #customer

output:
custid      custname        address1                       city            state 
----------- --------------- ------------------------------ --------------- ----- 
1           CustomerABC     4 Main Road                    Irvine          CA
2           CustomerDEF     123 Boundary Street            New York        NY
3           CustomerXYZ     8 25th Avenue                  San Francisco   CA