Gianluca Sartori (5/7/2009)

---

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