String Manipulation

[#1]

Hi All,

I have a field that consist of this kind of values, i want to create a new field with the following conditions:

Field1	rowid
BELL, B150145, BLACK, BIN J	1
BELL, B150145, WHITE A, BIN H, SnO2	2
BELL, C160135, WHITE A, BIN E	3
BELL, C150145, WHITE B, MASTER BIN	4
BELL, C160145, BLACK, BIN D, Aurora	5
BELL, C160145, MASTER BIN, Aurora	6
CELL,   C160145, BIN TS	7
PVM, SPR-215-WHT-U N	8

First I want to know the field values that has the word BIN then i want to get the letter/s after the word BIN, for example for rowids:

1=J, 2=H, 3=E, 5=D, 7=TS. the letter or letters after the word BIN should be only 1 or 2. (the letters will be the value of the new field)

if field1 has the word BIN on it but the latter part is not a single letter or a 2 letter combination like rowid: 4 and 6, this is an invalid Field1 value.

then if Field1 column doesn't consist of the word BIN we will not get this field value.

I want to do this in the load script

I hope you can help me on this.. Thank you in advance!!

  

BELL,   B150145, BLACK, BIN J

[adhudson]

Hi,

     I have attched the solution file below.

     It matches your requirement.

     Hope this helps you.

Regards

Andrew Hudson

[CELAMBARASAN]

Hi,

     Try using this expression for the result field

   Trim(SubField(SubField(Field1,'BIN',2),',')) as ResultField

Celambarasan

[#4]

i have solved your problem...yoy can have a look at the screen shot..if it is what you are looking for i will provide yoy the solution.

i have given the field values a name 'y' if it contains bin and if not then 'n'.

also calculated the characters after the BIN