I am trying to render the following line in the stored procedure written in javascript -
------Expected output------
select val1 from tableName where REGEXP_REPLACE(var1, '@|T|Z|\\(GMT-04:00\\))',' ')) >= date1
At present the code looks like this in the stored procedure -
----Actual code--------
SELECT val1 from tableName where REGEXP_REPLACE(var1,''@|T|Z|\\\\\\\\(GMT-04:00\\\\\\\\))'', '' '')) >= date1
The above code does result in the expected output, however I am trying to optimize this solution, and looking for cleaner way to achieve this final result. Any suggestions are appreciated. TIA.
Here are a couple of methods that might work for you.
SELECT
column1,
REGEXP_REPLACE(column1, '@|T|Z|\\(GMT-04:00\\)',' ') || '!' as r1,
LEFT(column1, LENGTH(column1)-13) || '!' as hack1,
iff(column1 ILIKE '%(GMT%', substr(column1, 1, position('(', column1)-3), column1 ) || '!' as hack2
FROM values
('2021-01-22 03:40:12.000 T(GMT-04:00)'),
('2021-01-22 03:40:12.000 @(GMT-04:00)'),
('2021-01-22 03:40:12.000 Z(GMT-04:00)'),
(to_char(current_timestamp))
the concat of | is to show the effect of white space matching.
| COLUMN1 | R1 | HACK1 | HACK2 |
|---|---|---|---|
| 2021-01-22 03:40:12.000 T(GMT-04:00) | 2021-01-22 03:40:12.000 ! | 2021-01-22 03:40:12.000! | 2021-01-22 03:40:12.000! |
| 2021-01-22 03:40:12.000 @(GMT-04:00) | 2021-01-22 03:40:12.000 ! | 2021-01-22 03:40:12.000! | 2021-01-22 03:40:12.000! |
| 2021-01-22 03:40:12.000 Z(GMT-04:00) | 2021-01-22 03:40:12.000 ! | 2021-01-22 03:40:12.000! | 2021-01-22 03:40:12.000! |
| 2022-01-21 18:50:43.554 -0800 | 2022-01-21 18:50:43.554 -0800! | 2022-01-21 18:50! | 2022-01-21 18:50:43.554 -0800! |