Is there a BigQuery version of isnumeric

user_6193392 0
Viewed 21576

I need to test if a field is numeric or not using standard SQL in BigQuery.

The example below works and is similar to what I have done in Cognos using TRANSLATE('mystring','1234567890.','') but its not very elegant.

SELECT
IF(LENGTH(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('1234.56','1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'0',''),'.',''))=0,
'A number',
'Not a number')
google-bigquery
3 Answers

You can use SAFE_CAST to try casting to a number. SAFE_CAST casts similar to CAST, but if casting fails, instead of erring null is returned.

For example you can do:

SAFE_CAST('1234567890' AS FLOAT64);

which will return 1.23456789E9

user_34115560

Thanks for both suggestions, both work a treat and I have gone for the SAFE_CAST option as it runs a fraction quicker.

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '1234.56' col UNION ALL
  SELECT '1234.' col UNION ALL
  SELECT '1234' col UNION ALL
  SELECT '.56' col UNION ALL
  SELECT '1234..56' col UNION ALL
  SELECT 'a1234.56' 
 )
 SELECT
   col,
   if(SAFE_CAST(col AS FLOAT64) is null,'Not a number', 'A number')
 FROM `project.dataset.table`
user_61933920

but its not very elegant

Below examples for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '1234.56' col UNION ALL
  SELECT '1234.' col UNION ALL
  SELECT '1234' col UNION ALL
  SELECT '.56' col UNION ALL
  SELECT '1234..56' col UNION ALL
  SELECT 'a1234.56' 
)
SELECT
  col,
  IF(LENGTH(REGEXP_REPLACE(col, r'[\d.]', '')) = 0, 'A number', 'Not a number') ,
  IF(REGEXP_CONTAINS(col, r'^\d*.?\d*$'), 'A number', 'Not a number') 
FROM `project.dataset.table`
user_52219440
Related