SQL tips and tricks 1

Come controllare se una colonna carattere contiene valori numerici

 WITH T_01 (C02, C05, c07)
    AS (VALUES ('12','12345', '1234567'),
               ('1x','12M45', '1234567'),
               ('12','12345', '12K4567'),
               ('1a','12X45', '123N567'),
               ('1d','1c345', '123d567'))
SELECT 
   C02, (CASE WHEN (TRANSLATE(trim(C02), 'NNNNNNNNNNX', '1234567890N')='NN')
             THEN 'is numeric' ELSE 'not numeric' END) C02_NUM,
   C05, (CASE WHEN TRANSLATE (trim(C05), 'NNNNNNNNNNX', '1234567890N')='NNNNN'
             THEN 'is numeric' ELSE 'not numeric' END) C05_NUM,
   C07, (CASE WHEN TRANSLATE (trim(C07), 'NNNNNNNNNNX', '1234567890N')='NNNNNNN'
             THEN 'is numeric' ELSE 'not numeric' END) C07_NUM
  FROM T_01

Result:

---
C02 C02_NUM     C05   C05_NUM     C07     C07_NUM    
12  is numeric  12345 is numeric  1234567 is numeric 
1x  not numeric 12M45 not numeric 1234567 is numeric 
12  is numeric  12345 is numeric  12K4567 not numeric
1a  not numeric 12X45 not numeric 123N567 not numeric
1d  not numeric 1c345 not numeric 123d567 not numeric
---