parsing name

gongxia649's Avatar
Newbie Member
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
0
gongxia649's Avatar
Newbie Member
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?
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
SPLIT Function for SQL Server
Code: SQL
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