Skip to main content
Topics#Tableau Desktop & Web AuthoringPergunta de Bill Germain
Bill Germain (PIK Sustainable LLC) perguntou em #Tableau Desktop & Web Authoring
22 de set. de 2021, 17:03

Trying to use regular expressions and an if/then/else if statement to reformat a zip code field containing both 5 and 9 digit zip codes. I want the resulting field to include both 5 digit and 9 digit codes, with the latter reformatted to: xxxxx-xxxx

Below is the calculation I created. Although the calculation shows as valid, the resulting field does not include the desired dash for 9-digit zip codes.

 

IF REGEXP_MATCH ([Zip],'[0-9]{5}')

 

THEN [Zip]

 

ELSEIF REGEXP_MATCH ([Zip],'[0-9]{9}')

 

THEN LEFT([Zip],5) + "-" + RIGHT([Zip],4)

 

END

7 respostas
  1. Ken Flerlage (Moxy Analytics)
    22 de set. de 2021, 19:02

    So, looks like they are all either displayed as XXXXXXXXX or XXXXX. In that case, you can do this:

     

    // Format the zip code
    IF LEN([Zip])=5 THEN
        [Zip]
    ELSE
        LEFT([Zip],5) + "-" + RIGHT([Zip], 4)
    END
Escrever uma resposta...
0/9000