[gongxia649]

hi, im running into a bit of a problem. i will like to string parse the street name into what you see below using DTS. How can i get around this?

12 north plaza boulevard apt.16

12|N|plaza blvd| apt16

[gongxia649]

12 north plaza boulevard apt.16

i want to split the address and put it into multiple fields.
address_number
12

suffixA
North

street_name
plaza blvd

suffixb
apt16


anyone can help?

[shabbir]

SPLIT Function for SQL Server

CREATE FUNCTION Split (@String nvarchar(4000), @Delimiter char(1))
Returns @Results TABLE (Items nvarchar(4000))
AS
Begin
   Declare @INDEX int
   Declare @Slice nvarchar(4000)
   SELECT @INDEX = 1
   IF @String IS NULL RETURN
   
   While @INDEX != 0
   Begin
  
      SELECT @INDEX = CharIndex(@Delimiter, @String)
      IF INDEX != 0
         SELECT @Slice = LEFT(@String, @INDEX - 1)
      else
         SELECT @Slice = @String
         INSERT INTO @Results(Items) VALUES (@Slice)
         SELECT @String = RIGHT(@String, Len(@String) - @INDEX)
      IF Len(@String) = 0 break
   End
   RETURN
End