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

Reply
Highlighted
White Belt

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

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.

Visitor

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

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.

 

Announcements
September release notes Check out the details of our latest product release. Click here!