Alternative String Functions to Replace

[shane_spencer]

Hi All - just wanted your suggestions for best /neatest code. I've got some data and sometimes the contents of one of the fields is preceded by 1. 2. or 3. Obviously I can't use PurgeChar as it's a string of 2 characters plus there's numbers in the data. I opted for using a replace - or more accurately 3 replaces (see below) - but I think there may be a more efficient way (especially if I had more strings to replace than those 3). I did think of an If statement combined with mid but that didn't seem any more efficient that what I'd got. Any better ideas?

TRIM(   Replace(  Replace( Replace(FIELDNAME,'1.','') ,'2.','')  ,'3.','')   ) As NEWFIELDNAME,

[tresesco]

May be using subfield(), considering dot (.) a separator, like:

Load

          SubField(Field, '.', 2) as NewField

[shane_spencer]

That would work in this case because there are no other dots (.) in the data, but it doesn't seem very robust. Thanks though.

[tresesco]

This?

Load

          Replace(Field, SubField(Field, '.', 1)&'.' , '') as NewField

[antoniotiman]

Hi Shane,

You can use MapSubstring() Function

like this

MapTable:
Mapping LOAD * Inline [
A,B
1.,
2.,
3.,
];

LOAD *,MapSubString('MapTable',Field) as MappedField Inline [
Field
1.00A
2.00B
3.00C
400
500
600];

Regards,

Antonio

[shane_spencer]

That's clever - I haven't used MapSubstring() function before.

[ahaahaaha]

Hi,

May be as variant

LOAD*,

If(IsNull(Left(Field, Index(Field, '.')-1))=0, Right(Field, Len(Field)-Index(Field, '.')), Field) as Newfield;

LOAD*Inline

[Field

1.fjfk

22.jjfkfl

3.345

44.3e4r

jfkgl

jdjdk

2354.5hytu

876.ryt5

];

Result

Regards,

Andrey

[shane_spencer]

That's very neat - as long as there's no other dots in my data to screw things up. Thanks!

[shane_spencer]

Interesting - very useful if not easy to read. Thanks.

[tresesco]

If you want to cut short from the first dot, you could try:

=Mid(Field, Index(Field,'.'))