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]

Try this may be:

SubField(MY_FIELD, ', ') as MY_FIELD_NEW

[marcus_sommer]

Use match() instead of wildmatch().

- Marcus

[hopkinsc]

Subfield doesnt work, there could be multiple values in a string, some might just be 2 values, others might have 6.

[mightyqlikers]

hi

use subfield(field_name,',') as newfield

Regards

S@M

[Chanty4u]

chk dis thread

[Chanty4u]

sry Replace function: Find Multiple strings and replace to blank

[hopkinsc]

Match only seems to pick up the IT line, it doesnt search other strings for IT (Sales, Marketing, IT).

what i want is to be able to select IT in the new field and that have all values in the original field with IT in the string to become a 'white' value

[sunny_talwar]

Doesn't matter how many values you have. If you do it in script it will create a seperate value for each of them. Will create a sample to show you

[Anonymous · Accepted solution]

may be like this?

if( MY_FIELD like '*Sales*','Sales',

  if(MY_FIELDlike 'IT*','IT')) as Dept;