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