2

I am looking into a use case of validating phone numbers for 3 Scandinavian countries (DK, SE, NO) which have between 8 and 10 digits, and the 2-digit country code in front.

I have looked into the PATINDEX function, which fails in Query Studio, but runs in a Query Activity. However, it returns 0 for even valid phone numbers. Have anyone used it before on MC, and can confirm? The regex within is tested and works as expected.

select 
id
,phone
,PATINDEX('%^[4][567]\d{8,10}$%',phone) as valid 
from Account_Salesforce
where phone is not null 

2 Answers 2

2

I think the issue comes in to what PATINDEX() allows (as well as what SFMC allows in PATINDEX). As it needs to start and end with %, using the hard 'end of string' ($) is not going to really work. I also found that \d digit as well as the bracket control values \d{8,10} do not seem to work.

My begrudging recommendation is to use a CASE statement along with the PATINDEX() and LEN() functions. It is far from efficient, but it should work and fit with your original line of thought:

select 
id
,phone
,CASE
    WHEN PATINDEX('%[4][567][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', phone) > 0 AND LEN(phone) = 10 THEN
    '10 digit'
    WHEN PATINDEX('%[4][567][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', phone) > 0 AND LEN(phone) = 12 THEN
    '12 digit'
    ELSE
    'Fail'
END as valid 
from Account_Salesforce
where phone is not null 

I don't have all the details into why your example does not work exactly, but the above with the CASE using LEN to ensure the hard stop at the char count works for sure. There MUST be a more efficient way to handle this, but I cannot think of a way off the top of my head.

1
  • 1
    Indeed, PATINDEX is not one of my mostly used SQL functions, but thanks to your advice I managed to make it work. Thank you, @Gortonington! Feb 4, 2021 at 19:13
1

To add to @Gortonington answer:

a) SFMC allows only for a minimal scope of RegEx characters (be it in PATINDEX or LIKE:

  1. % Wildcard that denotes zero, one or more characters
  2. _ Wildcard that denotes exactly one character
  3. [] Operator that captures any single character from the list specified inside
    • Operator that allows you to create a range in []
  4. ^ Operator that allows you to exclude []

You can find SFMC-focused use examples in my SFMC SQL docs.

b) Depending on the data quality in the source, you might want also to consider (and maybe even clean if you later want to use it for MobileConnect) the possible prefixes (00, 0, +)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.