BeastMode: Field contains lower case

swagner
swagner Contributor
in Magic ETL

Working on a exception report where item descriptions in our item file contain a lower case character.

 

Logic would work like this:

 

case

when `Description` LIKE '%[lower_case]%' then 'Yes'

else 'No'

end

 

Thanks!

Best Answer

Answers

  • Neeti
    Neeti Member

    Hi @swagner ,

     

    What is the issue here?

     

    Thanks,

    Neeti

  • swagner
    swagner Contributor

    @Neeti what are you asking?

  • Neeti
    Neeti Member

    @swagner , I cant see complete post, was just wondering is the lower case giving you any issue?

     

    Regards,

    Neeti

  • swagner
    swagner Contributor

    @Neeti the case statement I provided is simply an example of the logic I am attempting to build with a real beast mode.  This beast mode does not work as is.

  • GrantSmith
    GrantSmith Coach

    @swagner 

    I don't think this is doable with a regular expression in a Beast Mode however you can utilize a Magic ETL data flow with a replace text tile and a regex to remove any non-lowercase characters and then check if the value is NULL or not to determine if it doesn't or does have lowercase characters in it.

    Screen Shot 2020-03-24 at 9.50.28 AM.png

     

    Another option is to utilize a MySQL dataflow instead:

    select `holidayDescr`
    , case when `holidayDescr` REGEXP '[a-z]' then 'Y' else 'N' end as has_lowercase
    from `calendar_dates`

     

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

    @swagner 

     

    I suppose technically you could do a really long case statement based on each letter in the alphabet.

     

    case when `Description` like '%a%' OR
    `Description` like '%b%' OR
    `Description` like '%c%' OR
    `Description` like '%d%' OR
    `Description` like '%e%' OR
    `Description` like '%f%' OR
    `Description` like '%g%' OR
    `Description` like '%h%' OR
    `Description` like '%i%' OR
    `Description` like '%j%' OR
    `Description` like '%k%' OR
    `Description` like '%l%' OR
    `Description` like '%m%' OR
    `Description` like '%n%' OR
    `Description` like '%o%' OR
    `Description` like '%p%' OR
    `Description` like '%q%' OR
    `Description` like '%r%' OR
    `Description` like '%s%' OR
    `Description` like '%t%' OR
    `Description` like '%u%' OR
    `Description` like '%v%' OR
    `Description` like '%w%' OR
    `Description` like '%x%' OR
    `Description` like '%y%' OR
    `Description` like '%z%' THEN 'Y' ELSE 'N' END

     

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

    Of course I overthink the whole thing. @MarkSnodgrass has a much cleaner solution.

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

    @MarkSnodgrass THANKS!

Categories