CODEWITHSUNDEEP
sqlstringsnowflake-cloud-data-platform
Dinho
Dinho

Reputation: 724

How to remove text from value using SUBSTR in Snowflake?

I have a domain that looks like so:

www.google.com

I would like to remove 'www' from that and output would be as follows in a new field:

google.com
```

Current query is as follows but does not seem to work:
```
SELECT 
contact.ID,
contact.ACCOUNT_ID,
contact.NAME,
contact.EMAIL,
SUBSTR(contact.EMAIL,CHARINDEX('www',contact.EMAIL)+1,LEN(contact.EMAIL)) as Domain
FROM
contact
```

Upvotes: 0

Views: 1263

Answers (3)

Rajat
Rajat

Reputation: 5803

I find this more foolproof.

 set str='www.google.com';
 select case when $str ilike 'www.%' then right($str,len($str)-4) else $str end;

Upvotes: 1

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1608

Hi you can use regexp_replace, this will replace the 1st occurrence or wwww (depending upon your requirement)

select regexp_replace('www.google.com', 'www.','',1,1) as "result" from dual UNION
select regexp_replace('www.googleWWW.com', 'www.','',1,1) as "result" from dual;

Upvotes: 1

eshirvana
eshirvana

Reputation: 24568

If I understand correctly you can use REPLACE :

SELECT 
  contact.ID,
  contact.ACCOUNT_ID,
  contact.NAME,
  contact.EMAIL,
  REPLACE(contact.EMAIL , 'www.')
FROM
contact

Upvotes: 1

Related Questions