Need guidance in using REGEXP

Question

SELECT ID, web_name from table_name WHERE date='2022-02-02'
AND web_name LIKE '%https:%'

ID         WEB_NAME

1234      OFFERhttps://www.google.com/
3456      THEMEhttps://www.google.com/

When I run the above query, I'm getting the above results. I want the results to be displayed as below:

  ID         WEB_NAME
    
    1234      OFFER
    3456      THEME

I want https://www.google.com/ to be removed from the results. Any guidance would be of great help..

is "google.com" constant? does it change?

demircioglu
– demircioglu

2022-02-14 19:50:12 +00:00
Commented Feb 14, 2022 at 19:50 — demircioglu
– demircioglu, Commented Feb 14, 2022 at 19:50
Google.com is not constant. It will change

Jenifer
– Jenifer

2022-02-14 19:53:34 +00:00
Commented Feb 14, 2022 at 19:53 — Jenifer
– Jenifer, Commented Feb 14, 2022 at 19:53

Sergiu · Accepted Answer · 2022-02-14 19:51:14Z

1

Something like this should do it:

SELECT REGEXP_REPLACE('OFFERhttps://www.google.com/', 'http.*$', '');

I get the output as:

OFFER

Same when I run it for the other row. Docs here

answered Feb 14, 2022 at 19:51

Sergiu

4,7761 gold badge16 silver badges23 bronze badges

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

Comments

Simeon Pilgrim · Accepted Answer · 2022-02-15 07:14:08Z

You can use RGEXP_SUBSTR to match the "everything before http:", and depending if you want case insensitive matching (solution_1) or not (solution_2)the i clause should be added:

SELECT column1 as ID
    ,column2 as web_name 
    ,REGEXP_SUBSTR(web_name, '^(.*)https\:',1,1,'ie') as solution_1
    ,REGEXP_SUBSTR(web_name, '^(.*)https\:',1,1,'e') as solution_2
FROM VALUES 
    (1234, 'OFFERhttps://www.google.com/'),
    (3456, 'THEMEhttps://www.google.com/'),
    (1235, 'OFFERhTTps://www.google.com/'),
    (3455, 'THEMEhTTps://www.google.com/')
;

gives:

ID	WEB_NAME	SOLUTION_1	SOLUTION_2
1,234	OFFERhttps://www.google.com/	OFFER	OFFER
3,456	THEMEhttps://www.google.com/	THEME	THEME
1,235	OFFERhTTps://www.google.com/	OFFER
3,455	THEMEhTTps://www.google.com/	THEME

Which shows with any letter in the match not being the correct case, not match is made on the case sensitive match

Collectives™ on Stack Overflow

Need guidance in using REGEXP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related