locked
Split Text Field into Several Parts RRS feed

  • Question

  • I have a source field that has City, State Zip in it. For example, "Los Angeles, CA 90022" would be the text in a field.

    I'd like to write expressions to create calculated fields for each part of the field. I'd like to wind up with three fields: City, State, and Zip.

    I'm not sure of the command to use.

    Thank you for your help.


    Charles Allen Managing Consultant BKD Technologies
    Tuesday, May 11, 2010 2:18 PM

Answers

  • I have a source field that has City, State Zip in it. For example, "Los Angeles, CA 90022" would be the text in a field.

    HI Alllen is there any specific format rule that the three information is stored in ??? 

    like all separated with comma, or city and state separate with comma and zip code of  fixed length from right ??? 

    City, State, and Zip.

     

     

    declare @s varchar(100)
    select @s = 'Los Angeles, CA 90022'
    select charindex(',',@s) 
    SELECT 
    CITY = substring(@s,0,charindex(',',@s))
    ,STATE = substring(@s,charindex(',',@s)+1,len(@s)- (charindex(',',@s)+1 + 5))
    ,"ZIP CODE " = RIGHT(@s,5)

    neways check out the above code as per sample given ... 

    you can play with string functions and get result as desired .. 

    lets know if u are stuck .. 

    hope that helps .. kunal 

    • Edited by Kunal Joshi Tuesday, May 11, 2010 2:42 PM modify with code
    • Marked as answer by Charles Allen Tuesday, May 11, 2010 3:25 PM
    Tuesday, May 11, 2010 2:31 PM

All replies

  • I have a source field that has City, State Zip in it. For example, "Los Angeles, CA 90022" would be the text in a field.

    HI Alllen is there any specific format rule that the three information is stored in ??? 

    like all separated with comma, or city and state separate with comma and zip code of  fixed length from right ??? 

    City, State, and Zip.

     

     

    declare @s varchar(100)
    select @s = 'Los Angeles, CA 90022'
    select charindex(',',@s) 
    SELECT 
    CITY = substring(@s,0,charindex(',',@s))
    ,STATE = substring(@s,charindex(',',@s)+1,len(@s)- (charindex(',',@s)+1 + 5))
    ,"ZIP CODE " = RIGHT(@s,5)

    neways check out the above code as per sample given ... 

    you can play with string functions and get result as desired .. 

    lets know if u are stuck .. 

    hope that helps .. kunal 

    • Edited by Kunal Joshi Tuesday, May 11, 2010 2:42 PM modify with code
    • Marked as answer by Charles Allen Tuesday, May 11, 2010 3:25 PM
    Tuesday, May 11, 2010 2:31 PM
  • Try (the code assumes zip is always at the end).

    declare @test table (Col1 varchar(100))
     insert into @test values ('Los Angeles, CA 90022'), ('Alamo, NV')
     select T.Col1, F1.ZipCode, F4.City, F5.[State] from @Test T
     cross apply (select patindex('%[0-9][0-9][0-9][0-9][0-9]', T.Col1) as ZipPos) F
     cross apply (select case when F.ZipPos > 0 then substring(T.Col1,F.ZipPos, len(T.Col1)) end as ZipCode) F1
     cross apply (select case when F.ZipPos > 0 then substring(T.Col1,1, F.ZipPos - 1) else T.Col1 end as CityState) F2
     cross apply (select CHARINDEX(', ', F2.CityState) as StatePos) F3
     cross apply (select case when F3.StatePos > 0 then substring(F2.CityState, 1, F3.StatePos - 1) else F2.CityState end as City) F4
     cross apply (select case when F3.StatePos > 0 then substring(F2.CityState, F3.StatePos + 2, LEN(F2.CityState)) end as [State]) F5
     
    SQL Server 2005+

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, May 11, 2010 2:47 PM
  • Thank you for your immediate help. I did modify it a little bit but I never would have gotten anywhere close without your help.
    Charles Allen Managing Consultant BKD Technologies
    Tuesday, May 11, 2010 3:25 PM
  • Thank you for your reply.
    Charles Allen Managing Consultant BKD Technologies
    Tuesday, May 11, 2010 3:26 PM