Can QV detect characters within a record ?

[#1]

Hi,

Say for example that I have a field named Email_Address with many records and i wanted to identify incorrect email addresses.

For example I may want to count the number of @ characters in each records in order to flag any instances with more than one

Any thought appreciated

Thanks

Stuart

[#2]

fair point

[MarcoWedel]

Hi,

to verify email adresses you could use a regular expression solution in QlikView like

How to use regular expressions ( by Andrea Ghirardello

and test your email adresses with patterns found e.g. here:

php - Using a regular expression to validate an email address - Stack Overflow

the result could look like:

hope this helps

regards

Marco

[MarcoWedel]

one possible implementation:

tabEmailAddress:
LOAD *,
    If(MatchRegExp(eMailAddress, '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$'),'valid','invalid') as Valid
INLINE [
    eMailAddress
    john.doe@anydomain.com
    jane.doe@anydomain.com
    bob.doe@anydomaincom
    billdoe@anydomain.com
    stevedoe@anydomaincom
    no@no.de
    superman@@krypton.com
    supermankrypton.com
    mr.spock@vulkan.universe
];

VBS module:

Function MatchRegExp(targetString, patternSring)
  Dim re
  Set re = New RegExp
  re.Pattern = patternSring
  re.Global = False
  re.IgnoreCase = False
  MatchRegExp = re.Test(targetString)
End Function

hope this helps

regards

Marco

[MarcoWedel]

or like this:

tabEmailAddress:
LOAD *,
    If(IsEmailAddress(eMailAddress),'valid','invalid') as Valid
INLINE [
    eMailAddress
    john.doe@anydomain.com
    jane.doe@anydomain.com
    bob.doe@anydomaincom
    billdoe@anydomain.com
    stevedoe@anydomaincom
    no@no.de
    superman@@krypton.com
    supermankrypton.com
    mr.spock@vulkan.universe
];

Function IsEmailAddress(targetString) 
  Dim re
  Set re = New RegExp 
  re.Pattern = "^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$"
  re.Global = False
  re.IgnoreCase = False
  IsEmailAddress = re.Test(targetString) 
End Function 

hope this helps

regards

Marco

[MarcoWedel]

other RegExps found in the net, that claim to validate email addresses.

maybe someone would like to give it a try and post some comparison?:

\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b

^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$

^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$

(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])

[-0-9a-zA-Z.+_]+@[-0-9a-zA-Z.+_]+\.[a-zA-Z]{2,4}

^(?(")(".+?(?<!\\)"@)|(([0-9a-z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-z])@))"(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-z][-\w]*[0-9a-z]*\.)+[a-z0-9][\-a-z0-9]{0,22}[a-z0-9]))$

^[_A-Za-z0-9-\\+]+(\\.[_A-Za-z0-9-]+)*@[A-Za-z0-9-]+(\\.[A-Za-z0-9]+)*(\\.[A-Za-z]{2,})$;

hope this helps

regards

Marco