sqlnetezza

Extracting valid timestamps in SQL


I am working with Netezza sql. I have a table (my_table). For col_1, I only want to extract rows that are in the following format: 2020-01-01 12:00:00 and col_2 is only numbers

I am trying to do this with regexp_extract:

SELECT *
FROM my_table
WHERE regexp_extract(col_1, 
                     '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$') 
      IS NOT NULL;

SELECT *
FROM my_table
WHERE regexp_like(col_2, '^[0-9]+$');

Is there a more uniform way to do this such that it can work on multiple different versions of SQL? I.e. perhaps some versions of sql do not have the regexp_extract command


Solution

  • See simple example

    id col_1 col_2
    1 2020-01-01 12:00:00 123456
    2 2020-01-01 12:00:00 a123456
    3 2020-01-01 12:00:00 123456b
    3 2020-01-01 12:00:00 123c456
    11 2020-01-01w 12:00:00 123456
    12 02020-01-01w 12:00:00 123c456
    SELECT t.*
      ,translate(col_1,'0123456789','9999999999') c1
      ,replace(translate(col_2,'0123456789','9999999999'),'9','') c2
    FROM test t
    where not( 
            (translate(col_1,'0123456789','9999999999')='9999-99-99 99:99:99')
        and (replace(translate(col_2,'0123456789','9999999999'),'9','')='')
      )
    ;
    
    id col_1 col_2 c1 c2
    2 2020-01-01 12:00:00 a123456 9999-99-99 99:99:99 a
    3 2020-01-01 12:00:00 123456b 9999-99-99 99:99:99 b
    3 2020-01-01 12:00:00 123c456 9999-99-99 99:99:99 c
    11 2020-01-01w 12:00:00 123456 9999-99-99w 99:99:99
    12 02020-01-01w 12:00:00 123c456 99999-99-99w 99:99:99 c

    fiddle