Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   parsing name (http://www.go4expert.com/forums/parsing-name-t1114/)

gongxia649 3Aug2006 00:07

parsing name
 
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 3Aug2006 18:44

Re: parsing name
 
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 3Aug2006 19:01

Re: parsing name
 
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



All times are GMT +5.5. The time now is 16:00.