split city and state data from one filed into two fields

[ashok]

I want to split city and state in to two fields from one field as in below example:

field: ADDRESS

MAYVILLE WI
SPOKANE WA
FREEBURG IL
ST. LOUIS, MO
MILFORD C.T
FREEBURG, I.L


Results should look like this:

fileds: CITY | STATE

MAYVILLE | WI
SPOKANE | WA
FREEBURG | IL
ST.LOUIS | MO
MILDORD | CT
FREEBURG | IL

thank you
Ashok

[vmcburney]

I noticed in another thread that you have QualityStage installed. Why not run these fields through standard address parsing through a QualityStage plugin? It should be able to seperate the city from the state and deliver them back in seperate fields.

If you were doing it in DataStage you need to make certain assumptions. Are city and state always seperated by a space? Does the state always appear at the end of the string? Is state always a two character code?
In a routine I would split off the last two characters after the last space using the DCOUNT and FIELD commands. I would then remove any non alpha charcters from this string. I would then pass this to a state code lookup, either by hard coding state codes in a routine or doing a transformer lookup against a hash file. Anything that does not produce a valid state code gets rejected.
The remainder of the string becomes the city. Trim off from the end of the city name any non alpha characters (eg. ST.LOUIS, becomes ST.LOUIS).

[dsxdev]

A simple solution would be use a Column Export Stage which can export/ split a single column into multiple columns.
But the column to be split should have a format( fixed with or delimited).

[PilotBaha]

One word : QualityStage.

If you, as an alternative, try to "fish" <blank> + state code, what will you do if you have "Hartford CT" and "Hartford C.T" and "HArtford, CT" etc. etc. No easy way out..

Use QS, and sleep better at night :D

[ray.wurlod]

If you really want to do it in DataStage you can, though I'd fully support your using Quality Stage - after all, "bucketing" address components is one of the things it does supremely well. For those who don't have Quality Stage...

A "final delimited field" function is handy thing to have in one's tool kit. Here's a cut down version (with no error reporting).

Code: Select all

FUNCTION FinalDelimitedField(String, Delimiter)

* Author:  Ray Wurlod

If UnAssigned(String) Or IsNull(String) Or UnAssigned(Delimiter) Or IsNull(Delimiter)
Then
   Ans = @NULL
End
Else
   If Len(Delimiter) = 1
   Then
      FieldCount = DCount(String, Delimiter)
      Ans = Field(String, Delimiter, FieldCount, 1)
   End
   Else
      Ans = @NULL
   End
End

RETURN(Ans)

State could be derived, then, as FinalDelimitedString(Trim(Address)," ")

The remainder of the address (city) could be handled with something similar, to return all but the final delimited field.

Code: Select all

Field(TrimB(Address), " ", 1, Count(TrimB(Address), " "))

The TrimB function is insurance against the Address field containing trailing spaces.

[vmcburney]

And somewhere in there, either at the start or at the end, remove the comma delimiters from the city name.

Code: Select all

Address = EREPLACE(Address, ",", "")

[ashok]

thank you for help,

Can any one help me how to do this job in Quality Satage, as I tried using USAREA rule to seperate City and Sate it worked only on one record and passing remaining records as it is to result file,

[rasi]

Ashok

There is a separate forum for Quality Stage and post your questions there for quick reply. Not all knows Quality Stage in this forum to help you.

Thanks

[ray.wurlod]

Not all knows Quality Stage in this forum to help you.

Why not?!!!!