Need guidance in using REGEXP_REPLACE

Question

When I run the below SQL, I'm getting results mentioned below:

SELECT  distinct id,date,col_t FROM table1
WHERE col_t LIKE '%true%'
OR col_t LIKE '%https%'
OR col_t LIKE '%.html%'
OR col_t LIKE '%.html%'
OR col_t LIKE '%_____%' 
OR col_t LIKE '%null%'

ID     DATE                  COL_T

1     2022-02-02              true
2     2022-02-02              true
3     2022-02-02              PROMOTIONhttps://www.redbus.com/home
4     2022-02-02              google_goog_ob_27pc4https://www.google.com
5     2022-02-02              goog_gl_a1_store_id.html
6     2022-02-02              abc_xyz_def-example_____
7     2022-02-02              elp_car_parking_____
8     2022-02-02              elp1_car2_sum-grill_____
9     2022-02-02              two_abcd_slp_1_null_null_null_null

I want to replace true values with NULL and others with empty string ''. Can I use REGEXP_REPLACE to get the desired output?

I tried using

REGEXP_REPLACE(COL_T,'\.html|http.*$|_null.*$|____.$|true','')

AS COL_T. But I'm not getting accurate results.The results should look like below:

    ID     DATE                  COL_T


1     2022-02-02              NULL
2     2022-02-02              NULL
3     2022-02-02              PROMOTION
4     2022-02-02              google_goog_ob_27pc4
5     2022-02-02              goog_gl_a1_store_id
6     2022-02-02              abc_xyz_def-example
7     2022-02-02              elp_car_parking
8     2022-02-02              elp1_car2_sum-grill
9     2022-02-02              two_abcd_slp_1

Greg Pavlik · Accepted Answer · 2022-02-18 15:43:45Z

3

Try using Replace instead: https://docs.snowflake.com/en/sql-reference/functions/replace.html

SELECT  distinct id,
        date,
        CASE 
            WHEN col_t = 'true' then REPLACE(col_t, 'true', '')
            WHEN col_t = '*.html' then REPLACE(col_t, '*.html', '')
            ELSE NULL
        col_t 
FROM table1
WHERE 
        col_t LIKE '%true%'
    OR  col_t LIKE '%https%'
    OR  col_t LIKE '%.html%'
    OR  col_t LIKE '%.html%'
    OR  col_t LIKE '%_____%' 
    OR  col_t LIKE '%null%'

edited Feb 18, 2022 at 15:43

Greg Pavlik

11.3k2 gold badges14 silver badges31 bronze badges

answered Feb 18, 2022 at 9:44

kuldeep ghosh

1092 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need guidance in using REGEXP_REPLACE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related