replacing text in a string

[hopkinsc]

Hi all, i have a field with values such as..

Admin

Admin, IT

Audit, IT

Audit, IT, Marketing

Sales, Marketing, Admin

etc

i want to separate the string into its own value, i.e.

Admin

IT

Audit

Marketing

Sales

then store these new values into a field.

i have used the following..

if(Wildmatch(MY_FIELD,'*Sales*')>0,'Sales',

  if(wildmatch(MY_FIELD,'IT^')>0,'IT')) as Dept;

this works for all of the values apart from 'IT' as IT is also present in 'Audit'.

does anyone know of a way i can separate IT and not include Audit?

[sunny_talwar]

Script:

Table:

LOAD *,

  SubField(MY_FIELD, ', ') as MY_FIELD_NEW;

LOAD * Inline [

MY_FIELD

Admin

Admin, IT

Audit, IT

Audit, IT, Marketing

Sales, Marketing, Admin

] (delimiter is '|');

Output:

[Chanty4u]

Load
SubField(NewString,'#',1) as MainCategory,
SubField(NewString,'#',2) as NewCategory;
Load SubField(String,',') as NewString;
Load * Inline [
String
"

Admin

Admin, IT

Audit, IT

Audit, IT, Marketing

Sales, Marketing, Admin

"
]

[hopkinsc]

thats great, the LIKE function worked.

thanks very much

[Chanty4u]

yes ur ri8 sunny...