Skip to content

    • Magic ETL

    Magic ETL

    Filtering strings that contain letters

    Member
    edited April 2024 in Magic ETL

    I want to filter out strings that contain letters in my ETL.

    e.g.

    Serial

    112233

    445566PN

    In the ETL

    Add Formula

    Column Name: IsInt

    CASE

    WHEN serialLIKE ('%[A-Z]%') THEN 0

    ELSE 1

    END

    EDIT
    CASE
    WHEN SERIAL~* ([a-z]) THEN 0
    ELSE 1
    END

    This is working for A-Z and is case insensitive.
    Next question, is how do I filter out other characters?

    Specifically, one serial number has a % sign in it, which is the wildcard symbol…

    Tagged:

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answers

    • Coach
      Answer ✓

      If you're wanting to only find values which are all numbers you can use a regular expression:

      1. CASE WHEN REGEP_LIKE(`SERIAL`, '^[0-9]+$') THEN 1 ELSE 0 END

      If you're wanting to include numeric like characters as well (decimals and dashes) you can use \d instead of [0-9]

      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Coach
      Answer ✓

      ^ - Start of the text

      $ - End of the text

      Combining them together in the rexex above simple enforces that the entire string is made of numbers instead of just finding numbers anywhere in the string

      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**

    Answers

    • Member

      This seems to work.

      CASE
      WHEN SERIAL~* ([a-z]) THEN 0
      ELSE 1
      END

    • Coach
      Answer ✓

      If you're wanting to only find values which are all numbers you can use a regular expression:

      1. CASE WHEN REGEP_LIKE(`SERIAL`, '^[0-9]+$') THEN 1 ELSE 0 END

      If you're wanting to include numeric like characters as well (decimals and dashes) you can use \d instead of [0-9]

      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Member

      Hi Grant,

      Thanks, that works well.

      Do you know where I can find information on where symbols like ^ and $ are explained?

      Cheers,

      Chris

    • Coach
      Answer ✓

      ^ - Start of the text

      $ - End of the text

      Combining them together in the rexex above simple enforces that the entire string is made of numbers instead of just finding numbers anywhere in the string

      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Member

      Thanks Grant, much appreciated.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories