Link to home
Start Free TrialLog in
Avatar of fb1990
fb1990

asked on

Strip Specific Values

Hello Experts,


Can someone please help me strip specific text from a URL using SQL server code or Oracle SQL code? I have the follow URLs and would like strip hhtps:// and everything from /mail to the end

 

https://www.securemyonlinesite.com/mail/web/person/home
https://www.my-onlinesite.org/mail-text/web/person/home
https://www.cbqmyonline2.com/mail-text/web/person


Avatar of fb1990
fb1990

ASKER

The final URL should look like the following:

www.securemysite.com
www.my-onlinebanking.org
www.cbqonline2.com

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fb1990

ASKER

Thank you very much, worked like a charm.  I'd like the SQL Server version as well...
I don't know SQL Server SQL that well.  I know enough to know it really doesn't support regex

I was able to locate a few examples of "sql server remove domain from url" on the web.  I didn't post any links because there are a few methods out there.  No idea which one would be considered 'better' than others.

Using REGEXP_SUBSTR here is (naturally) more elegant, sure, but if you want to avoid this quite "expensive" function call, then you might want to use just "SUBSTR" and "INSTR", like:

with r as
(select 'https://www.securemyonlinesite.com/mail/web/person/home' url     from dual   union select 'https://www.my-onlinesite.org/mail-text/web/person/home' url     from dual   union select 'https://www.cbqmyonline2.com/mail-text/web/person' url     from dual) select substr(r.url, instr(r.url, '//') + 2, instr(r.url, '/', 1, 3) - instr(r.url, '//') - 2)   from r;

Open in new window


The more rows you need to process, the longer your queries will run using REGEXP functions...

Avatar of fb1990

ASKER

Thanks, Alex.  I tried your solution is says "Third argument in SUBSTR() cannot be negative"

You tried that very SQL or with some other data?

Was it on an Oracle DB or SQL Server?

Avatar of fb1990

ASKER

Ah, yes with my database.  So, yes with other data.  And Good morning
Avatar of fb1990

ASKER

I tried in BigQuery.  That makes the difference.  I tried in SQL server, i got "'instr' is not a recognized built-in function name"

There could be a problem when there is not path at all in the URL/string; This should work even with those...

with r as
(select 'https://www.securemyonlinesite.com/mail/web/person/home' url     from dual   union select 'https://www.my-onlinesite.org/mail-text/web/person/home' url     from dual   union select 'https://www.cbqmyonline2.com/mail-text/web/person' url     from dual   union select 'https://www.cbqmyonline2.com' url     from dual) select substr(r.url,               instr(r.url, '//') + 2,               decode(instr(r.url, '/', 1, 3), 0, length(r.url) + 1, instr(r.url, '/', 1, 3)) - instr(r.url, '//') - 2)   from r;

Open in new window


The SQL Server "instr" would be "CHARINDEX":

https://www.sqlines.com/oracle/functions/instr