NOT IN function of MySQL not working in Magic Transform MySQL of DOMO.

LokeDomo
LokeDomo ⚪️
in Data Platform & Data Science

Hi,

In one of my coloumn im getting all numeric values and few of them are string values(bad data). When im trying to use NOT IN function of MySQL not working in Magic Transform MySQL of DOMO. Please suggest me the solution.

 

Thanks.

Comments

  • Unknown
    Unknown

    Hi LokeDomo,

     

    It sounds like you have a text column that should contain only numbers, but there are some rows where that field contains non-numbers. And you're trying to filter out or perhaps nullify the non-number rows. Is that correct?

     

    You can use regular expressions to check for letters as below:

    SELECT
    `RecordID`,
    `NumberAsText`,
    `NumberAsText` regexp '^[0-9]+$',
    cast(case when `NumberAsText` regexp '^[0-9]+$' = 1 then `NumberAsText` end as signed) as `NumberAsNumber`
    from `numberastext`

     

    In the above snippet, `NumberAsText` regexp '^[0-9]+$' checks to see if NumberAsText contains ONLY numerals 0 to 9 and returns 1 if so. In the last colum above, I check to see if the field contains only numbers and return the number if so; otherwise, return NULL. And finally cast as a signed integer. 

     

    Hope that helps.

     

Categories