select 
PARSENAME(REPLACE('12 test street<br>knighton<br>london<br>sw3 4rg<br>United Kingdom','<br>','.'),1) as ADDRESS1

quote:

---

Originally posted by SukhCRM

Thanks guys for the reply. If PARSENAME only works up to 4 delimeters, what is the best way for me to achieve this result? I'm thinking I could do something like

SELECT LEFT(ShippingAddress,CHARINDEX('<br>',ShippingAddress)-1) AS ADDRESS1
,LEFT(SUBSTRING(ShippingAddress,(CHARINDEX('<br>',ShippingAddress)+4),LEN(ShippingAddress)),CHARINDEX('<br>',SUBSTRING(ShippingAddress,(CHARINDEX('<br>',ShippingAddress)+4),LEN(ShippingAddress)))-1) AS ADDRESS2

---

drop function fnParseArray
CREATE FUNCTION dbo.fnParseArray (@array VARCHAR(1000),@separator VARCHAR(10))
RETURNS @T Table (occ int,col1 varchar(50))
AS 
BEGIN
DECLARE @separator_position INT 
DECLARE @array_value VARCHAR(1000)
declare @occurence int =1 -- to get the Nth occurence
if (left(@array,len(@separator))=@separator)
begin
        set @array=stuff(@array,1,len(@separator),'')
end
if (right(@array,len(@separator))<>@separator)
begin
        SET @array = @array + @separator
end
WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
BEGIN
 SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
 SELECT @array_value = LEFT(@array, @separator_position - 1)
 INSERT into @T VALUES (@occurence,@array_value) 
 set @occurence = @occurence + 1
 SELECT @array = STUFF(@array,1, @separator_position + len(@separator)-1, '')
END
RETURN 
END

-- Example to call
select * from 
dbo.fnParseArray ('12 test street<br>knighton<br>london<br>sw3 4rg<br>United Kingdom','<br>')
order by occ desc

-- or
select * from 
dbo.fnParseArray ('12 test street<br>knighton<br>london<br>sw3 4rg<br>United Kingdom','<br>')
where occ=1