declare @a varchar(200)
set @a = 'Address1,address2,address3,address4'
select parsename(replace(@a,',','.'), 4)
select parsename(replace(@a,',','.'), 3)
select parsename(replace(@a,',','.'), 2)
select parsename(replace(@a,',','.'), 1)

quote:

---

Originally posted by mflammia

Hi thanks for replying. My apologies, but I may not of explained this correctly or I have simply not understood the code provided properly.

The name of the field in the DB is called _Venue and in side that is address data like

Row 1 Layer Marney Tower
Row 2 High Chelmer Shopping Centre, Chelmsford
Row 3 Epping Forest Visitor Centre, Paul's Nursery Road, Loughton, Essex IG10 4A

So I am not sure what to add into the line

set @a = ‘address1,address2,address3,address4’

as the information and the format it always different.

---

SELECT PARSENAME(REPLACE(addressfield,' ','.'),4),
PARSENAME(REPLACE(addressfield,' ','.'),3),
PARSENAME(REPLACE(addressfield,' ','.'),2),
PARSENAME(REPLACE(addressfield,' ','.'),1)
FROM YourTable

declare @t table (row int, _Venue varchar(100))
insert @t
Select 1, 'Layer Marney Tower' union all
Select 2, 'High Chelmer Shopping Centre, Chelmsford' union all
Select 3, 'Epping Forest Visitor Centre, Paul''s Nursery Road, Loughton, Essex IG10 4A'

SELECT	PARSENAME(REPLACE(_Venue,',', '.'),4) address1
	,PARSENAME(REPLACE(_Venue, ',', '.'),3) address2
	,PARSENAME(REPLACE(_Venue, ',', '.'),2) address3
	,PARSENAME(REPLACE(_Venue, ',', '.'),1) address4
FROM @t

address1                           address2                address3                     address4
------------------------------------------------------------------------------------------------------------
NULL                               NULL                    NULL                         Layer Marney Tower
NULL                               NULL                    High Chelmer Shopping Centre Chelmsford
Epping Forest Visitor Centre       Paul's Nursery Road     Loughton                     Essex IG10 4A