parse a CSZ field 1

[aspnetuser]

I have a field
san fransisco, CA 19111


I need to create three fields in a view, C S Z

The delimiters are always , space, space.

Any help on this would be greatly appreciated.

 

[donutman]

Passing a list of values to a Stored Procedure (Part II) faq183-5207
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jshinall]

I don't know if this helps but here's something I just threw together. There are a few assumptions.

Declare @CSZ as varchar(50)

Set @CSZ = 'san fransisco, CA 19111'

Select replace(substring(@CSZ,1,charindex(',',@CSZ)),',','')
Select replace(substring(@CSZ,charindex(',',@CSZ),4),', ','') -- Assumes State is always 2 letters and always follows a comma space
Select right(@CSZ,5) -- Assumes zip is always last 5

 

[dbomrrsm]

create view myview
as
select C =
 substring('san fransisco, CA 19111',1,
patindex('%, %','san fransisco, CA 19111')-1),
S = substring('san fransisco, CA 19111',patindex('%, %','san fransisco, CA 19111')+2,
len('san fransisco, CA 19111')-1 - patindex('%, %','san fransisco, CA 19111')-
patindex('% %',reverse('san fransisco, CA 19111'))),
Z = right('san fransisco, CA 19111',
patindex('% %',reverse('san fransisco, CA 19111'))-1)

[blue]DBomrrsm[/blue]

 

[aspnetuser]

dbomrrsm
I get the following error:
Invalid lenght parameter passed to substring function.

I get this because I’ve got some {CSZ}'s that don't have a comma.
I need to check for the comma somehow and code unknown if it is missing.

Here was the suggestion in crystal reports and it worked fine.

StringVar State;
If InStr({CSZ},",") = 0 then
State := "Unknown"
Else
State := split(trim(split({CSZ},",")[2])," ")[1];

 

[aspnetuser]

can that be done in SQL
I am more accustome to CR

thanks!

 

[aspnetuser]

I am stuck, I figured I post back in hopes of a solution.

 

[dbomrrsm]

look at the case statement

[blue]DBomrrsm[/blue]

 

[aspnetuser]

I can;t figure it out. Can you help me with the CASE?

 

[aspnetuser]

can you take my crystal code and convert it to SQL as a CASE?

 

[vongrunt]

Using a view to patch typos and misformatted data = suicide, from dozen or so reasons.

How about making 3 separated table fields (city, state, zip) and keeping them that way?

 

[aspnetuser]

thats what i want to do.
three seperate fields.
Thats what dbomrrsm helped on. The problem is sometimes a comma does not exist in the field for every record. I need to say IF their is a "," then do not perform the substring etc...

i can't change the table structure, it is what it is.
I need to compensate for the single field with bad data and parse it out. can you help?

 

[clapag22]

This do it? (this code also works for 5, 9 or 10 digit zips):

select 
case
when myCSZfield not like '%,%' then myCSZfield
else rtrim(ltrim(left(myCSZfield, patindex('%,%', myCSZfield) - 1))) 
end as city, 
case
when myCSZfield not like '%,%' then null
else rtrim(ltrim(reverse(substring(reverse(myCSZfield), patindex('% %', reverse(myCSZfield)) + 1, 2)))) 
end as state,
case
when myCSZfield not like '%,%' then null
else rtrim(ltrim(reverse(left(reverse(myCSZfield), patindex('% %', reverse(myCSZfield)))))) 
end as zip
from mytable

 

[ProdAir]

If you won't ever encounter dots "." in your data, you can delimit your data with dots and use parsename.

 

[donutman]

Now that's creative, ProdAir!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ProdAir]

Thanks donutman! I hope my suggestion helped though.