Removing special characters in a field using MDM Assignments

[Former Member]

Hi Experts,

I have a small requirement.

We have a field called Telephone number which has the special characters and the data is maintained like +966(54)12345 or (001)12345 etc.,..

Now the requirement is to remove the spl characters in that particualr field without manual intervention (automatical) by runnign an assignment.

i.e after running the assignment, the field value should look like 9665412345 or 00112345 etc.,.

Is this possible using assignments?

If possible, then kindly guide with some logic for the same.

Thanks in advance,

Sravan

[Former Member · Accepted solution]

Hi,

See, Using single Assignment it seems not to be worked. As every-time when i entered into single Assignment it gives Assignment as TRUE OR FALSE. But obviously If you are looking into Validation it works.

So, for the same you need to create three Assignments:

I am assuming every field contain only "+" as one character only, I mean 98, 1111 try this Assignment.

IF(FIND(FIELD NAME, "PLUS SIGN" ), LEFT(FIELD NAME, FIND(FIELD NAME, "PLUS SIGN" ) - 1)&MID(FIELD NAME, FIND(FIELD NAME, "+" ) + 1), FIELD NAME)

Put, PLUS SIGN = +

I am assuming every field contain only "(" as one character only, I mean (011)12345. It should not contain +(011)12(345)

IF(FIND(FIELD NAME, "(" ), LEFT(FIELD NAME, FIND(FIELD NAME, "(" ) - 1)&MID(FIELD NAME, FIND(FIELD NAME, "(" ) + 1), FIELD NAME)

LLY, I am assuming for ")".

IF(FIND(FIELD NAME, ")" ), LEFT(FIELD NAME, FIND(FIELD NAME, ")" ) - 1)&MID(FIELD NAME, FIND(FIELD NAME, ")" ) + 1), FIELD NAME)

So, if you have any field with value +966(54)12345 or (001)12345, after running all above three Assignments it will look like 9665412345 or 00112345

Note: Ideally, before entering data into MDM you can do this during MDM Import Manager by right click on this source field ->Set Value Conversion Filter--> Normalize which will take care of chars like +, (, ), etc.

Kindly let me know if you still face any issue.

Thanks and Regards,

Mandeep Saini

[former_member205403]

Hi,

Exression Logic for Removing Special Characters from a field using assignment will be very complex and you may have to create many assignments to do this.

So, I would suggest not to allow user to enter values with special characters. You can handle it using Validations. If you are entering Data through data manager or Portal then using Validations you can achieve this.

Expression for such kind of validation will be

HAS_ALL_CHARS(XXXX,"0","9")

here, replace XXXX with your field. This expression will not allow user to enter any special character in this field.

Also, If you are importing data using Import Manager then definitely you can normalize this field data using Conversion filters, As suggested by Mandeep.

Regards,

Shiv