Find if data in column fulfills a datatype condition in sql

Question

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

Any one if them would be fine teradata and snowflake preferred. — Mohammed Shaheer, Jul 13 '22 at 04:30

score 1 · Answer 1 · answered Jul 13 '22 at 04:20

1

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).

answered Jul 13 '22 at 04:20

Tim Biegeleisen

502,043
27
286
360

Thanks but i am unable to run in teradata is it different with other db? Pls help – Mohammed Shaheer Jul 13 '22 at 04:31
1

In Teradata you could use `TRYCAST(Product_id AS INTEGER)` or `REGEXP_SIMILAR(Product_id,'.*\D.*') = 0` or `REGEXP_SUBSTR(Product_id,'\D') IS NULL` – Fred Jul 13 '22 at 16:12

score 1 · Accepted Answer · answered Jul 13 '22 at 04:30

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.*')

Find if data in column fulfills a datatype condition in sql

2 Answers2