I think you should use some kind of slit function.
I use this one, but I'm sure there's a better one out there.
CREATE FUNCTION [dbo].[fSplit]
(
@List VARCHAR(6000),
@SplitOn VARCHAR(5)
)
RETURNS @RtnValue TABLE
(
ID INT identity(1,1),
Value VARCHAR(100)
)
AS
BEGIN
WHILE (Charindex(@SplitOn,@List)>0)
BEGIN
INSERT INTO
@RtnValue (value)
SELECT
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
SET @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
END
INSERT INTO
@RtnValue (Value)
SELECT
Value = ltrim(rtrim(@List))
RETURN
END
You can use it like this:
SELECT dbo.fSplit(MyAddressField,'*')
FROM MyTable
-- Gianluca Sartori