Find if data in column fulfills a datatype condition in sql-CodePudding

I have a table product with a column product id which is string datatype as below.

Product_id
101 
102
102a

I would like to know if there is any way to take all values in product_id which cannot fill the condition of integer which is 102a value as it cannot be converted to integer

Query something like

select product_id from product where product_id <> integer

And output should be as

Product_id
102a

CodePudding user response：

In snowflake:

select column1 as Product_id
from values 
    ('101'),
    ('102'),
    ('102a')
where try_to_number(Product_id) is null;

gives

PRODUCT_ID
102a

TRY_TO_NUMBER will return null, if the input string fails to convert, thus, you could use that in your WHERE clause.

If you want a REGEX form, you can use:

where rlike(Product_id, '.*\\D.*')

CodePudding user response：

You could use a regex like here:

SELECT Product_id
FROM product
WHERE Product_id !~ '\D';

This would return only records having Product_id values not having any non digit characters (i.e. the characters it does have are all digits).