Splitting String into different substrings of variablelength

pmadhavi · Post by **pmadhavi** » Fri May 26, 2006 3:02 pm

Hi
I have an Address column in the table which is getting split into 3 diffrent fields city, state, zip.

Now we need to split the value from Address column and load it to the 3 columns.
The value of Address looks like
21785 Filigree Court, Suite 103 Ashburn, Virginia 20147-6214
4431-A Brookfield Corp Drive Chantilly, Virginia 20151

it means the zip code is not always 5digit

Please suggest how to split them.

ray.wurlod · Post by **ray.wurlod** » Fri May 26, 2006 5:07 pm

Use the Field() function. To get the rightmost field, count the delimiters (space characters).

Code: Select all

Field(InLink.Address, " ", Count(InLink.Address, " ") + 1, 1)

An even better approach would be a QualityStage STAN (standardization) job, which can accurately parse free-form address data. Do you have QualityStage installed? It's the perfect tool for this job.

pmadhavi · Post by **pmadhavi** » Tue May 30, 2006 9:20 am

Hi Ray,
Thanks for the help. I am able to fetch the zipcode from the address as it is the last substring in the address column.

But when I am mapping the complete address column to a flat file, it is not fetching the full string rather its throwing an error like

read_delimited()-invalid quotes, row 61 column address='Sumner county courthouse'

The actual value of the column is
"Sumner County Courthouse Gallatin, TN 37066". But only 'Sumner County Courthouse" is getting loaded to the flat file.
I tried with hash file too. No change.

pls suggest me why am i getting this error.
Is it because the no. of spaces between the substrings?

DSguru2B · Post by **DSguru2B** » Tue May 30, 2006 10:01 am

That is because Ray's solution for "Sumner County Courthouse Gallatin, TN 37066" is going to give you 37066". The sequential file will shout because only one " is found. It always looks for pairs. In your sequential file output, provide the quote character as 000. That will calm down the sequential file stage but you still need to handle that situation. Maybe trim the quotes.

ray.wurlod · Post by **ray.wurlod** » Tue May 30, 2006 4:45 pm

... and you will need to Trim() the trailing quote character from the result generated by the Field() function in this case.

pmadhavi · Post by **pmadhavi** » Wed May 31, 2006 9:25 am

ray.wurlod wrote:... and you will need to Trim() the trailing quote character from the result generated by the Field() function in this case.
...

Hi
The problem is Address field contains many spaces in between.
I have a function called CHANGE() which I am using in a transformer to remove the spaces. But before getting the address into the transformer itself it is throwing the error as given below.

DataStage Job 1258 Phantom 3572
Program "JOB.1124724363.DT.1403136011.TRANS1": Line 49, Improper data type.
Attempting to Cleanup after ABORT raised in stage Test2..Transformer_3
DataStage Phantom Aborting with @ABORT.CODE = 3

Its behaving in a weird manner.
The steps I followed:
1. tried loading data from ODBC to flat file(successful)
2. tried loading data from ODBC to flat file via transformer(successful)
3. if I use a second transformer for lookup and then load data to flat file, it is throwing up the above error.

then I keep on removing one after the other and go to the step 1, still it throws the same error.

I dont understand the reason.
Pls help me

ray.wurlod · Post by **ray.wurlod** » Wed May 31, 2006 3:34 pm

The number of spaces is irrelevant. The Field() function I provided counts them in order to yield the final delimited field. If there are trailing spaces, you might like to apply a TrimB() function before applying the Field() function.

Code: Select all

Field(TrimB(InLink.Address), " ", Count(TrimB(InLink.Address), " ") + 1, 1)