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

 

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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.)

 

Answer 5

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

 

Answer 6

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.

Answer 7

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