1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

parsing name

Discussion in 'SQL Server' started by gongxia649, Aug 2, 2006.

  1. gongxia649

    gongxia649 New Member

    Joined:
    Aug 2, 2006
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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
     
  2. gongxia649

    gongxia649 New Member

    Joined:
    Aug 2, 2006
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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?
     
  3. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,287
    Likes Received:
    364
    Trophy Points:
    83
    SPLIT Function for SQL Server
    Code:
    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
    
     

Share This Page