Special Characters Validations

[Former Member]

Hi, I need to write a validation that will give an error when user enters any funny characters e.g @, $ and about 300 more since we have multiple langueu2019s.

Currently I have the following working validation;

HAS_ANY_CHAR(Field Name,"@","$")=False

What I want to know is if there are an easier way to achieve my goal of blacking the non valid characters we identified?

Any help will be appreciated.

[Former Member · Accepted solution]

Hi,

This solution works greate; However, I can not a spaces between text. I tried to replace "-" with a space but no luck.

IF(HAS_ALL_CHARS(fieldname,"A","z") OR ( HAS_ALL_CHARS(fieldname,"A","z") AND FIND(fieldname,"-")), TRUE , FALSE)

Any ideas?

Still habe a look at Bhupal proposal.

Many thanks

[Former Member · Accepted solution]

HI Chris,

Can you tell me whether your text field will have only one space or there could be n number of spaces because if the number of spaces is fixed then we can achieve this else I dont think this is possible.

As far as transformations goes then it is only used in Matching mode and it will not actually change the field values. So as per my knowledge, Transformations and Starategies cannot be used in this context.

Regards,

Jitesh Talreja

[Former Member · Accepted solution]

Hi, thanks for the reply;

Only want to allow for one space between characters.

Thank you.

[Former Member · Accepted solution]

Hi Chris,

Check the below expression

IF(HAS_ALL_CHARS(FieldName,"A","z") OR (HAS_ALL_CHARS(MID(FieldName,1,FIND(FieldName," ")-1),"A","z") AND HAS_ALL_CHARS(RIGHT(FieldName,FIND(FieldName," ")),"A","z")),TRUE,FALSE)

OR

IF(HAS_ALL_CHARS(FieldName,"A","z") OR (HAS_ALL_CHARS(LEFT(FieldName,FIND(FieldName," ")-1),"A","z") AND HAS_ALL_CHARS(RIGHT(FieldName,FIND(FieldName," ")),"A","z")),TRUE,FALSE)

Its working perfectly fine at my end. If you find difficult to understand the logic then I would be happy to explain it to you.

Regards,

Jitesh Talreja

Edited by: Jitesh Talreja on Aug 11, 2009 12:12 PM

[Former Member · Accepted solution]

Thanks again, but I cant get it to work.

Maybe if you explain the logic then it would make more sense.

Regards

[Former Member · Accepted solution]

Hi Chris,

IF(HAS_ALL_CHARS(FieldName,"A","z") OR (HAS_ALL_CHARS(LEFT(FieldName,FIND(FieldName," ")-1),"A","z") AND HAS_ALL_CHARS(RIGHT(FieldName,FIND(FieldName," ")),"A","z")),TRUE,FALSE)

There are two conditions:

1. If the value contains characters from ASCII range A to z which is covered in first condition (before OR). This condition will fail if there exists some space in the value.

2. If there is some space then we will first break the value into two halves and then check the two halves individually for ASCII range A-z. This is what i have done in second condition (after OR). With the help of FIND, i am finding the positon of space and using LEFT, i am extracting first half i.e. from first character to the position space-1. Similarly, using RIGHT, i am extracting second half.

Give me the examples you are using to test this validation and let me check them at my end.

Regards,

Jitesh Talreja

[Former Member · Accepted solution]

Thanks for this got it to work, I was testign the validation and forgot that only one space is allowed.

Thanks for all your help

[Former Member]

Hi,

Firstly if you want to replace "-" with Blank I mean " " . For Example ABC-DEF-GHI with ABC DEF GHI then it is Possible using Import Manager, After map your Source field with target field, right Click on your Source Field Set Value Conversion Filter--> Replace then put in Find what: - and Put in Replace with: click here one time Space bar. You will see all your values corresponding to "-" will replaced with " ". it is working fine at my end.

Further, you want error for characters like "@" and "$".

Write this Validation.

IF(FIND(Field Name,"@") OR FIND(Field Name, "$"), FALSE , TRUE). it is also working fine at my end.

Hope it will help you....

Thanks,

Mandeep Saini

[Former Member]

Hi Chris,

Can you please update us on your issue?

Regards,

Jitesh Talreja

[Former Member]

Hi Chris,

In data manager in match mode

In Trasformation tab we remove special characters using TOKENS

right click transfermation pane create name like "remove -"

ex: AB-PQR-D, we don't want to "-" replace to ABPQRD

Under TOKENS:

FROM TO

"-" keep emty place

In RULE tab give for FIELD: whatever name given in transformation: "remove -"

In strategies pane will give rule name.

select the duplicate records in record pane, right click press match tab, select the check box

go to merge tab merge the records.

OUTPUT: ABPQRD and merge the others into one record.

Regards

Bhupal.

[Former Member]

Thank you,

HAS_ALL_CHARS(fieldname,"A","z") - works great but I want "-" to be excluded e.g.

My value should be ABC-DEF-GHI

How do I do this

[Former Member]

Hi Chris,

Check the below expression

IF(HAS_ALL_CHARS(fieldname,"A","z") OR ( HAS_ALL_CHARS(fieldname,"A","z") AND FIND(fieldname,"-")), TRUE , FALSE)

Regards,

Jitesh Talreja

Edited by: Jitesh Talreja on Jul 30, 2009 3:33 PM

[Former Member]

Hi Chris

If you dont want to have the special characters saved in MDM you can also use import map to screen such special characters.

for the field mapped apply replcae convertor and replcae all such special character with blank.

best regards

Ravi

[Former Member]

Hi Chris,

Try using HAS_ALL_CHARS(s1, n2, n3) which validates whether the string is within the range specified by n2 and n3.

E.g. HAS_ALL_CHARS(fieldname,"A","z") which check whether the value for the fieldname contains only characters.

Regards,

Jitesh Talreja