Data Split

[#1]

HI,

I have data something like the below

0046-APT-0543

WM-APT-5171

0043

TMD-VILLA-0005

POARR-VILLA

HENNUR-0009

HENNUR

0063-0011

C1100

I need to display only the last four numbers that are before '-'.

Answer will be something like below.

0543

5171

0043

0005

Null

0009

Null

0011

Null

Please find the attachment.

Regards,

Keerthi KS

[maxgro]

try with

load

  *,

  if(SubStringCount(s, '-')>=3, mid(s, index(s, '-', SubStringCount(s, '-')-1)+1),

  if(SubStringCount(s, '-')>=1, SubField(s, '-', -1),

  null())) as t

inline [

s

0046-APT-0543

WM-APT-5171

0043

TMD-VILLA-0005

POARR-VILLA

HENNUR-0009

HENNUR

0063-0011

C1100

0072-Villa-B9-M

1-2-Villa-B9-M

1-2-3-Villa-B9-M

1-2-3-4-Villa-B9-M

];

[ogster1974]

Should POARR-VILLA return VILLA or null?  In the original requirement the value should have been null.

[maxgro]

yes you're right

POARR-VILLA          --> null

but why?

0072-Villa-B9-M      --> B9-M

and

POARR-VILLA          --> null

[ogster1974]

As a guess VILLA is just a type identifier rather than a record ID.  There seems a mixture of data quality in the example records hence the cleaning requirement.

That's why I suggested as long as the output is always 4 char in length you can can check if its a valid 4 length char by seeing if the 5th char from the right is a "-".  That would handle all the cases displayed to date including the extra one he came up with.

[maxgro]

maybe this

load

  s,

  if(len(t)<>4, null(), t) as t;

load

  *,

  if(SubStringCount(s, '-')>=3, mid(s, index(s, '-', SubStringCount(s, '-')-1)+1),

  if(SubStringCount(s, '-')>=1, SubField(s, '-', -1),

  null())) as t

inline [

s

0046-APT-0543

WM-APT-5171

0043

TMD-VILLA-0005

POARR-VILLA

HENNUR-0009

HENNUR

0063-0011

C1100

0072-Villa-B9-M

1-2-Villa-B9-M

1-2-3-Villa-B9-M

1-2-3-4-Villa-B9-M

];