Parse data

[vinaymanchinila]

Hi,
I am reading from a table and one of the columns has the following sample data, I need to parse the data and extract 3 fields , the delimiter is '-'

Source: 205-51-510203 Required Output: 205 51 510203
Source: 201-1-10001 Required Output: 201 1 10001
Source: 202-14 Required Output: 202 14
Source:207 Required Output: 207


Any ideas, I am trying to use the Field function.

Thanks,

[DSguru2B]

Use convert.

Code: Select all

CONVERT("-", " ", in.Col)

[vinaymanchinila]

Hi,

I am not trying to repalce the '-', I need to parse or extract 3 columns from one , if it is 12-34-5, then I need 12, 34 and 5 as separate column values.

Thanks,

[vinaymanchinila]

I have got this to work using the Field function, thanks !

[swades]

One more option is Load to Flat file,while reading that file define "-" as delimiter.I guess its work.

[sshettar]

i guess this could also be done in just the sequential file stage by specifying the properties

Record level

Final delimiter = end

Field Defaults

Delimiter = -
Quote = none

and in columns tab specify your metadata ( ie 3 columns)

i think this should work .

correct me if i'm wrong

[DSguru2B]

It might, but no need to land it to a flat file. It can be taken care of in the transformer itself.
vinaymanchinila , the sample data and output that you provided gave me the impression that the hypen (-) needs to be replaced with a space and hence my resolution. Had you made it clear in your original post that the output needs to go into 3 different columns, the solution would have been different. Anywho, glad you got it working. Care to share the solution for future searchers ???

[vinaymanchinila]

Hi,
I have used the Field function, my source column has 3 values delimited by '-' and I needed 3 output columns , the following are the derivations for the 3 output columns,

SourceCol=201-232-9090


Output Col1=201 ---->Field(SourceCol,'-',1)
Output Col2=232---->Field(SourceCol,'-',2)
Output Col3=9090---->Field(SourceCol,'-',3)

Thanks,