Получить домен из URL в Oracle SQL
У меня есть база данных, содержащая URL-адреса веб-сайтов. Из этих URL-адресов я хотел бы извлечь доменное имя. Вот два (тихих разных) примера:
Для этого я использую регулярное выражение и функции REGEXP_SUBSTR и REGEXP_REPLACE, которые предоставляет Oracle. Я использую replace для замены предыдущего http[s] www. с пустой строкой (удаление ее). Затем я использую подстроку, чтобы получить строку между началом и первым / или если нет / вся струна. Мой код выглядит так:
Все работает так, как ожидалось, за исключением того факта, что мое регулярное выражение не исключает / :
Substring domainname from URL SQL
Some data has www. , some doesn’t. Some has .com / .com.sg / .com.ul , some doesn’t.
How do I extract just the name e.g. google , yahoo , marketwatch , bing , bbc using SQL?
3 Answers 3
Using MS SQL Server syntax of CHARINDEX and SUBSTRING you could do something like.
(Deliberately overly split-up to mak eeach step obvious.)
You can use the Replace function in SQL to remove the www. if it doesn’t exist it will leave the string as it is.
EDIT
Sorry I missed out the ending — based on the sample data you have provided this will extract the name:
-
The Overflow Blog
Linked
Related
Hot Network Questions
Subscribe to RSS
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.3.11.43304
By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.
Как вытащить домен из сайта sql
I had to combine some of the previous answers , plus a little more hackery for my data set . This is what works for me , it returns the domain and any sub-domains:
Explanation ( cause non-trivial SQL rarely makes sense ):
SUBSTRING_INDEX(target_url, ‘/’, 3) — strips any path if the url has a protocol
SUBSTRING_INDEX(THAT, ‘://’, -1) — strips any protocol from THAT
SUBSTRING_INDEX(THAT, ‘/’, 1) — strips any path from THAT ( if there was no protocol )
SUBSTRING_INDEX(THAT, ‘?’, 1) — strips the query string from THAT ( if there was no path or trailing / )
Sorry, you have been blocked
This website is using a security service to protect itself from online attacks. The action you just performed triggered the security solution. There are several actions that could trigger this block including submitting a certain word or phrase, a SQL command or malformed data.
What can I do to resolve this?
You can email the site owner to let them know you were blocked. Please include what you were doing when this page came up and the Cloudflare Ray ID found at the bottom of this page.
Cloudflare Ray ID: 7a6f6f336cd177b0 • Your IP: Click to reveal 88.135.219.175 • Performance & security by Cloudflare