none
Need help in SQL Query Spliting

    Question

  • Hi 

    I have a Field in a Person table it includes city,state zip in one field 

    Like 

    Table Person

    Field:  Person.city_state_zip

     Covina,CA 91722

             Pasadena,CA 91101-9243

              NULL

    Dallas, TX 32456-0101

    but I want split that data into separate fields city,state,zip to map to the destination table

    Plz help me how can I add that spliting code in the source query

    Thanks in advance

     

    Thursday, June 17, 2010 2:20 AM

Answers

  • Try:

    SELECT
      c1,
      SUBSTRING(c1, 1, CHARINDEX(',', c1) - 1) AS city,
      LTRIM(SUBSTRING(c1, CHARINDEX(',', c1) + 1, PATINDEX('%[0-9]%', c1) - CHARINDEX(',', c1) - 1)) AS [state],
      SUBSTRING(c1, PATINDEX('%[0-9]%', c1), 10) AS zip
      
    FROM
      (
      VALUES
      ('Covina,CA 91722'),
      ('Pasadena,CA 91101-9243'),
      (NULL),
      ('Dallas, TX 32456-0101')
      ) AS T(c1);
    GO

    AMB

    • Proposed as answer by KiranKumar.Y Thursday, June 17, 2010 2:57 AM
    • Marked as answer by KJian_ Monday, June 28, 2010 1:50 AM
    Thursday, June 17, 2010 2:49 AM
    Moderator
  • Since SQL Server does not have built-in regular expressions*, this kind of thing tends to take a lot of grunt work, and the solutions often are not very robust in the presence of malformed data.  Hunchback's solution looks pretty good, but beware what happens if you put a number in the city part, for example.  (An "Invalid length parameter" error occurs.)  This isn't really to fault the particular solution.  This is pretty much par for the course when using SUBSTRING/CHARINDEX/etc. to split fields out of a string.  If some of your data is malformed and you want to use parsing techniques like these, you've got more work to do...  Test thoroughly!

    *Note that there are ways to get regular expressions in SQL Server through .NET CLR integration.  There are many articles on the net concerning this topic.

    (Note that Tom's solution produces incorrect output if you feed it 'El Paso, TX 12345' because it does not account for a space being in the city part.)

     

    • Proposed as answer by Naomi NModerator Thursday, June 17, 2010 3:43 AM
    • Marked as answer by KJian_ Monday, June 28, 2010 1:50 AM
    Thursday, June 17, 2010 3:01 AM
    Moderator
  • My solution for the similar problem:

    declare @t table (c1 varchar(500))
    insert @t values ('SFO,CA 92345-2345'),
    
    (NULL), ('New york,NY 01899'),('Seatle,Wasington 37902-2222'),('Sanfransicko')
    
    select c1, F1.City, F5.[State],F4.Zip from @t 
    cross apply (select Case when c1 is null then 0 else charindex(',',c1) end as CommaPos) F0
    cross apply (select Case when F0.CommaPos = 0 then c1 else SUBSTRING(c1,1,F0.CommaPos - 1) end as City) F1
    cross apply (select Case when F0.CommaPos = 0 then c1 else SUBSTRING(c1,F0.CommaPos + 1,LEN(c1)) end as Rest) F2
    cross apply (select PATINDEX('%[0-9]%',F2.Rest) as ZipStart) F3
    cross apply (select Case when ZipStart > 0 then substring(Rest, ZipStart, len(Rest)) else NULL end as Zip) F4
    cross apply (select Case when ZipStart > 0 then substring(Rest, 1, ZipStart-1) else NULL end as [State]) F5
     
    


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

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Monday, June 28, 2010 1:50 AM
    Wednesday, June 23, 2010 4:19 PM
    Moderator

All replies

  • Hi there,

    try below script, not tested:

    declare @citystatezip varchar(50)

    set @citystatezip = 'Pasadena,CA 91101-9243'


    select case when @citystatezip IS not null then substring(@citystatezip, 1, charindex(',', @citystatezip) - 1) else '' end as City,
           case when @citystatezip IS not null then substring(@citystatezip, charindex(',', @citystatezip) + 1, 2) else '' end as [State],
           case when @citystatezip IS not null then substring(@citystatezip,charindex(' ', @citystatezip),LEN(@Citystatezip)) else '' end as [ZipCode]

    Assuming "State" represents only 2 characters here.

    Make sure that the format for your column Person.city_state_zip values must be like [city,st zipcode] otherwise you may get wrong results.

    Hope it helps.


    Kiran
    Thursday, June 17, 2010 2:45 AM
  • Try:

    ;with x (city_state_zip)
    as
    (
    	select 'Covina,CA 91722'	union all
    	select 'Pasadena,CA 91101-9243'	union all
    	select 'Dallas, TX 32456-0101'
    ), y
    as
    (
    	select
    		replace (city_state_zip, ', ', ',') as city_state_zip
    	from
    		x
    )
    select
    	*
    ,	left (city_state_zip, charindex (',', city_state_zip) - 1) as city
    ,	substring (city_state_zip, charindex (',', city_state_zip) + 1, 2) as state
    ,	right (city_state_zip, len (city_state_zip) - charindex (' ', city_state_zip)) as zip
    from
    	y
    
    

    Tom https://mvp.support.microsoft.com/profile/Tom.Moreau
    Thursday, June 17, 2010 2:48 AM
  • Try:

    SELECT
      c1,
      SUBSTRING(c1, 1, CHARINDEX(',', c1) - 1) AS city,
      LTRIM(SUBSTRING(c1, CHARINDEX(',', c1) + 1, PATINDEX('%[0-9]%', c1) - CHARINDEX(',', c1) - 1)) AS [state],
      SUBSTRING(c1, PATINDEX('%[0-9]%', c1), 10) AS zip
      
    FROM
      (
      VALUES
      ('Covina,CA 91722'),
      ('Pasadena,CA 91101-9243'),
      (NULL),
      ('Dallas, TX 32456-0101')
      ) AS T(c1);
    GO

    AMB

    • Proposed as answer by KiranKumar.Y Thursday, June 17, 2010 2:57 AM
    • Marked as answer by KJian_ Monday, June 28, 2010 1:50 AM
    Thursday, June 17, 2010 2:49 AM
    Moderator
  • Since SQL Server does not have built-in regular expressions*, this kind of thing tends to take a lot of grunt work, and the solutions often are not very robust in the presence of malformed data.  Hunchback's solution looks pretty good, but beware what happens if you put a number in the city part, for example.  (An "Invalid length parameter" error occurs.)  This isn't really to fault the particular solution.  This is pretty much par for the course when using SUBSTRING/CHARINDEX/etc. to split fields out of a string.  If some of your data is malformed and you want to use parsing techniques like these, you've got more work to do...  Test thoroughly!

    *Note that there are ways to get regular expressions in SQL Server through .NET CLR integration.  There are many articles on the net concerning this topic.

    (Note that Tom's solution produces incorrect output if you feed it 'El Paso, TX 12345' because it does not account for a space being in the city part.)

     

    • Proposed as answer by Naomi NModerator Thursday, June 17, 2010 3:43 AM
    • Marked as answer by KJian_ Monday, June 28, 2010 1:50 AM
    Thursday, June 17, 2010 3:01 AM
    Moderator
  • thanks for the reply

    but I have a question in that field I have NULL values 

    I'm selecting the query 

    Like Source Table: SELECT p.fname,p.lname,p.address1,p.city_state_zip(use splitting code) from Person p

    Destination Table: SELECT fname,lname,address1,city,state,zip from PersonContact

    but need to use that splitting code in my Source Table query

    Plz help me how I can use that 

    Thanks in advance

     

    Thursday, June 17, 2010 3:04 AM
  • thanks for the reply

    but I have a question in that field I have NULL values 

    I'm selecting the query 

    Like Source Table: SELECT p.fname,p.lname,p.address1,p.city_state_zip(use splitting code) from Person p

    Destination Table: SELECT fname,lname,address1,city,state,zip from PersonContact

    but need to use that splitting code in my Source Table query

    Plz help me how I can use that 

    Thanks in advance 

    Hi,

    Have you tried Hunchback's solution? I think it meets your requirement.

    SELECT fname,lname,address1,
     SUBSTRING(city_state_zip, 1, CHARINDEX(',', city_state_zip) - 1) AS city,
     LTRIM(SUBSTRING(city_state_zip, CHARINDEX(',', city_state_zip) + 1, PATINDEX('%[0-9]%', city_state_zip) - CHARINDEX(',', city_state_zip) - 1)) AS [state],
     SUBSTRING(city_state_zip, PATINDEX('%[0-9]%', city_state_zip), 10) AS zip
    FROM Person

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, June 23, 2010 6:01 AM
  • My solution for the similar problem:

    declare @t table (c1 varchar(500))
    insert @t values ('SFO,CA 92345-2345'),
    
    (NULL), ('New york,NY 01899'),('Seatle,Wasington 37902-2222'),('Sanfransicko')
    
    select c1, F1.City, F5.[State],F4.Zip from @t 
    cross apply (select Case when c1 is null then 0 else charindex(',',c1) end as CommaPos) F0
    cross apply (select Case when F0.CommaPos = 0 then c1 else SUBSTRING(c1,1,F0.CommaPos - 1) end as City) F1
    cross apply (select Case when F0.CommaPos = 0 then c1 else SUBSTRING(c1,F0.CommaPos + 1,LEN(c1)) end as Rest) F2
    cross apply (select PATINDEX('%[0-9]%',F2.Rest) as ZipStart) F3
    cross apply (select Case when ZipStart > 0 then substring(Rest, ZipStart, len(Rest)) else NULL end as Zip) F4
    cross apply (select Case when ZipStart > 0 then substring(Rest, 1, ZipStart-1) else NULL end as [State]) F5
     
    


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

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Monday, June 28, 2010 1:50 AM
    Wednesday, June 23, 2010 4:19 PM
    Moderator