In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Identifying hidden special characters in a field

JHAROHIT
7 - Meteor

Hi,

 

I have a requirement of identifying certain special set of characters which are allowed in a field, rest if  any other special character comes in the data field should be raised as a defect.

 

Please see the below set of data:

 

NameLENGTHResult
Lee4Error
Linda6Error
Ad3Error
Lee3Pass
Linda5Pass
Ad2Pass

 

My code is 

if
REGEX_MATCH(trim([FirstName]),'[A-Za-z0-9\.\·\`\,\,\ \-\\ÀÈÌÒÙàèìòùÁÉÍÓÚÝáéíóúýÂÊÎÔÛâêîôûÃÑÕãñõÄËÏÖÜŸäëïöüŸ¡¿çÇŒœßØøÅ寿ÞþÐð]+') = 0 

then 'F'
else 'T'
endif

 

The first three records are being raised a defect(coming from a systemic data), although i am unable to see any special character.

For testing purpose,I manually entered the names and they are getting passed, doing length check on those shows a difference.

 

Any help in identifying the root cause of this error and identify the special character so that i can pass that as well in the code.

Also any other perspective if i am missing, please let me know.

 

Thank you.

3 REPLIES 3
ChrisTX
16 - Nebula
16 - Nebula

To get the ASCII or Unicode value for hidden characters, use a text editor like Notepad++

 

And/or you can use the Alteryx function CharToInt to get the Unicode number of the hidden character.   See Conversion Functions (alteryx.com)

 

CharToInt

CharToInt(s): Returns the number that matches the input Unicode® character s.

Example
  • CharToInt("B") returns 66 (U+0042 'Latin Capital Letter B').

  • CharToInt("©") returns 169 (U+00A9 'copyright sign').

 

If you post a workflow or other file with hidden characters, I can provide an example.

 

 

 

Chris

JHAROHIT
7 - Meteor

Thank you, Chris.

 

That helps.

 

Using notepad++, i was able to identify there were some hidden special characters as below:

 

'? ' at beginning of name

 
 
 

Is there a way that i can get rid of these special characters ? I tried using the replace or contains function, but it is not able to catch them.

 

Appreciate your help.

 

Regardsm

Rohit Jha.

ChrisTX
16 - Nebula
16 - Nebula

It may be easier to specify the characters you want to keep, like a-z, A-Z, underscore, dash, 0-9, etc

 

Try the RegEx tool with Replace mode

 

Chris

Labels
Top Solution Authors