Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос: Выделение домена из ссылки.  [new]
hider
Member

Откуда:
Сообщений: 23
Коллеги, подскажите, пожалуйста, как выделить адрес вплоть до домена 4го уровня:
К примеру:
имеется: https://sdfsdf.ssdfsdf.0.cs-ellpic.yandex.net/market_FJ9_bx\\\4kllSmCPMcRsdfsdfB3xzQ_100x100.jpg
надо получить: cs-ellpic.yandex.net

sdfsdf.ssdfsdf.0.cs-ellpic.yandex.net - вот эту часть я получил, а дальше туплю. И может можно оптимизировать мой запрос, а то выборка из очень большой таблицы:


DECLARE @name NVARCHAR(max)
DECLARE @char bigint
SET @name = 'https://0.cs-ellpic.yandex.net/market_FJ9_bx\\\4kllSmCPMcRB3xzQ_100x100.jpg'


SELECT SUBSTRING( (SUBSTRING ((@NAME),1,LEN(@name)-
LEN (SUBSTRING( (SUBSTRING( (@name), CHARINDEX('/',(@name))+2 ,LEN(@name))), CHARINDEX('/',(SUBSTRING( (@name), CHARINDEX('/',(@name))+2 ,LEN(@name)))) ,LEN(SUBSTRING( (@name), CHARINDEX('/',(@name))+2 ,LEN(@name))))
)) ),

CHARINDEX('/',(SUBSTRING ((@NAME),1,LEN(@name)-
LEN (SUBSTRING( (SUBSTRING( (@name), CHARINDEX('/',(@name))+2 ,LEN(@name))), CHARINDEX('/',(SUBSTRING( (@name), CHARINDEX('/',(@name))+2 ,LEN(@name)))) ,LEN(SUBSTRING( (@name), CHARINDEX('/',(@name))+2 ,LEN(@name))))
)) ))+2 ,

LEN(SUBSTRING ((@NAME),1,LEN(@name)-
LEN (SUBSTRING( (SUBSTRING( (@name), CHARINDEX('/',(@name))+2 ,LEN(@name))), CHARINDEX('/',(SUBSTRING( (@name), CHARINDEX('/',(@name))+2 ,LEN(@name)))) ,LEN(SUBSTRING( (@name), CHARINDEX('/',(@name))+2 ,LEN(@name))))
)) ))
11 янв 13, 09:37    [13751683]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
hider
Коллеги, подскажите, пожалуйста, как выделить адрес вплоть до домена 4го уровня
...
надо получить: cs-ellpic.yandex.net

Странно, мне казалось, доменом четвертого уровня в данном случае будет 0.cs-ellpic.yandex.net

Понять, что у вас там чариндексами наверчено, очень сложно, но базовый принцип подойдет такой:

1. Найти первый одиночный форвард-слеш и отбросить все, что за ним, включительно.
2. Найти первый двойной форвард-слеш и отбросить все, что перед ним, включительно.
3. Найти червертую точку справа и отбросить все, что идет перед ней, включительно.

Откройте для себя функцию PATINDEX(), мне кажется она здесь лучше подойдет. Ах да, еще на PARSENAME() посмотрите, тоже может пригодиться.
11 янв 13, 10:39    [13752058]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а еще reverse очень помогает
11 янв 13, 12:05    [13752730]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
hider,

Да вы какой-то скобочный маньяк :-) Каждое появление переменной @name не обязательно было заворачивать в скобки.

Мой вариант:
declare @names table (id int, name nvarchar(max));

insert @names values(1, 'https://sdfsdf.ssdfsdf.0.cs-ellpic.yandex.net/mark/et_FJ9_bx\\\4kllSmCPMcRsdfsdfB3xzQ_100x100.jpg');
insert @names values(2, 'https://www.sql.ru/forum/actualthread.aspx?tid=996164');
insert @names values(3, 'ftp://sql.ru/forum/actualthread.aspx?tid=996164');
insert @names values(4, 'x://y/z');

-- Если раскомментировать звёздочку, будут видны все этапы вычислений
select n.id, n.name, isnull(t9.third_part + '.', '') + isnull(t7.second_part + '.', '') + t5.first_part as req_name --, *
from @names n
     cross apply ( select stuff(n.name, 1, charindex('/', n.name) + 1, '') as no_prefix ) t1
     cross apply ( select left(t1.no_prefix, charindex('/', t1.no_prefix) - 1) as hostname ) t2
     cross apply ( select reverse(t2.hostname) as reverse_hostname ) t3
     cross apply ( select charindex('.', t3.reverse_hostname) as first_dot ) t4
     cross apply ( select case when t4.first_dot = 0 then t2.hostname else reverse(left(t3.reverse_hostname, t4.first_dot - 1)) end as first_part,
                          case when t4.first_dot = 0 then null else stuff(t3.reverse_hostname, 1, t4.first_dot, '') end as first_remain ) t5
     cross apply ( select charindex('.', t5.first_remain) as second_dot ) t6
     cross apply ( select case when t6.second_dot = 0 then reverse(t5.first_remain) else reverse(left(t5.first_remain, t6.second_dot - 1)) end as second_part,
                          case when t6.second_dot = 0 then null else stuff(t5.first_remain, 1, t6.second_dot, '') end as second_remain ) t7
     cross apply ( select charindex('.', t7.second_remain) as third_dot ) t8
     cross apply ( select case when t8.third_dot = 0 then reverse(t7.second_remain) else reverse(left(t7.second_remain, t8.third_dot - 1)) end as third_part ) t9;


Насколько этот запрос оптимален, понятия не имею. Вообще строковые преобразования — далеко не самое сильное место T-SQL. Для обработки очень большого массива данных, наверное, лучше бы привлечь другие средства.
11 янв 13, 13:26    [13753535]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Гость333,

Айпишники неправильно выделяет.
11 янв 13, 13:38    [13753644]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Гавриленко Сергей Алексеевич
Гость333,

Айпишники неправильно выделяет.

Точно, как-то забыл про них :-)
declare @names table (id int, name nvarchar(max));

insert @names values(1, 'https://sdfsdf.ssdfsdf.0.cs-ellpic.yandex.net/mark/et_FJ9_bx\\\4kllSmCPMcRsdfsdfB3xzQ_100x100.jpg');
insert @names values(2, 'https://www.sql.ru/forum/actualthread.aspx?tid=996164');
insert @names values(3, 'ftp://sql.ru/forum/actualthread.aspx?tid=996164');
insert @names values(4, 'x://y/z');
insert @names values(5, 'xaxa://127.0.0.1/i');
insert @names values(6, 'xexe://127.0.1/i/u/i');
insert @names values(7, 'xyxy://1a.12.13.14/i/u/i');

-- Если раскомментировать звёздочку, будут видны все этапы вычислений
select n.id, n.name,
       case when ip2.is_ip_address = 1
            then t2.hostname
            else isnull(t9.third_part + '.', '') + isnull(t7.second_part + '.', '') + t5.first_part
       end as req_name --, *
from @names n
     cross apply ( select stuff(n.name, 1, charindex('/', n.name) + 1, '') as no_prefix ) t1
     cross apply ( select left(t1.no_prefix, charindex('/', t1.no_prefix) - 1) as hostname ) t2
     cross apply ( select parsename(t2.hostname, 1) as ip_1,
                          parsename(t2.hostname, 2) as ip_2,
                          parsename(t2.hostname, 3) as ip_3,
                          parsename(t2.hostname, 4) as ip_4 ) ip1
     cross apply ( select case when ip1.ip_1 not like '%[^0-9]%' and ip1.ip_2 not like '%[^0-9]%' and ip1.ip_3 not like '%[^0-9]%' and ip1.ip_4 not like '%[^0-9]%'
                                    and ip1.ip_1 is not null and ip1.ip_2 is not null and ip1.ip_3 is not null and ip1.ip_4 is not null
                               then 1
                               else 0
                          end as is_ip_address ) ip2    
     cross apply ( select reverse(t2.hostname) as reverse_hostname ) t3
     cross apply ( select charindex('.', t3.reverse_hostname) as first_dot ) t4
     cross apply ( select case when t4.first_dot = 0 then t2.hostname else reverse(left(t3.reverse_hostname, t4.first_dot - 1)) end as first_part,
                          case when t4.first_dot = 0 then null else stuff(t3.reverse_hostname, 1, t4.first_dot, '') end as first_remain ) t5
     cross apply ( select charindex('.', t5.first_remain) as second_dot ) t6
     cross apply ( select case when t6.second_dot = 0 then reverse(t5.first_remain) else reverse(left(t5.first_remain, t6.second_dot - 1)) end as second_part,
                          case when t6.second_dot = 0 then null else stuff(t5.first_remain, 1, t6.second_dot, '') end as second_remain ) t7
     cross apply ( select charindex('.', t7.second_remain) as third_dot ) t8
     cross apply ( select case when t8.third_dot = 0 then reverse(t7.second_remain) else reverse(left(t7.second_remain, t8.third_dot - 1)) end as third_part ) t9;
11 янв 13, 13:55    [13753793]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
Strangers
Member [заблокирован]

Откуда: Україна
Сообщений: 2613
declare @names table (id int, name nvarchar(max));

insert @names values(1, 'https://sdfsdf.ssdfsdf.0.cs-ellpic.yandex.net/mark/et_FJ9_bx\\\4kllSmCPMcRsdfsdfB3xzQ_100x100.jpg');
insert @names values(2, 'https://www.sql.ru/forum/actualthread.aspx?tid=996164');
insert @names values(3, 'ftp://sql.ru/forum/actualthread.aspx?tid=996164');
insert @names values(4, 'x://y/z');
insert @names values(5, 'xaxa://127.0.0.1/i');
insert @names values(6, 'xexe://127.0.1/i/u/i');
insert @names values(7, 'xyxy://1a.12.13.14/i/u/i');

;with asd as
(-- отбросим "//" и всё что после первого одиночного "/"
	SELECT ID, name as name_source, SUBSTRING(name, PATINDEX ('%//%', name)+2,CHARINDEX('/', name,PATINDEX ('%//%', name)+2)-PATINDEX ('%//%', name)-2) as name
	from @names
)
-- нет больше четырех уровней - наш клиент
SELECT id, name_source as name, name as req_name
FROM asd
WHERE name not like '%.%.%.%.%'
	UNION
-- больше четырех уровней - отберем только последних 4
SELECT id, name_source, 
	Right(name,
	(select top 1number
	from master.dbo.spt_values k
	WHERE k.type = 'P'
		and SUBSTRING(REVERSE(z.name),1,number) like '%.%.%.'
		and SUBSTRING(REVERSE(z.name),1,number) not like '%.%.%.%.'
	ORDER BY number)-1)
FROM asd z
WHERE name like '%.%.%.%.%'
11 янв 13, 15:31    [13754775]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Strangers,

У вас из четырёхсоставных имён сайтов (типа petya.narod.yandex.ru) не выделяется трёхсоставная часть (narod.yandex.ru).
11 янв 13, 15:42    [13754886]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
declare @names table (id int, name nvarchar(max), slashed_name as name + '/');

insert @names values(1, 'https://sdfsdf.ssdfsdf.0.cs-ellpic.yandex.net/mark/et_FJ9_bx\\\4kllSmCPMcRsdfsdfB3xzQ_100x100.jpg');
insert @names values(2, 'https://www.sql.ru/forum/actualthread.aspx?tid=996164');
insert @names values(3, 'ftp://sql.ru/forum/actualthread.aspx?tid=996164');
insert @names values(4, 'x://y/z');
insert @names values(5, 'xaxa://127.0.0.1/i');
insert @names values(6, 'xexe://127.0.1/i/u/i');
insert @names values(7, 'xyxy://1a.12.13.14/i/u/i');
insert @names values(8, '1a.12.13.14/i/u/i');
insert @names values(9, '1a.12.13.14');
insert @names values(10, '://');
insert @names values(11, '/');

select
 isnull(substring(slashed_name, a.s, b.e - a.s), name)
from
 @names n cross apply
 (select isnull(nullif(patindex('%://%', slashed_name), 0) + 3, 1) as s) a cross apply
 (select isnull(nullif(charindex('/', slashed_name, a.s), 0), len(name)) as e) b;
11 янв 13, 16:14    [13755233]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
Strangers
Member [заблокирован]

Откуда: Україна
Сообщений: 2613
Гость333
Strangers,

У вас из четырёхсоставных имён сайтов (типа petya.narod.yandex.ru) не выделяется трёхсоставная часть (narod.yandex.ru).

Это просто идея или другой принцип решения задачки, а кол-во выделяемых уровней можно отрегулировать условиями "WHERE"

Там боков еще, наверное, немеряно. Ну, например, без "//" и последующего одинарного "/" не работает
Не хотелось городить лишнего
11 янв 13, 16:18    [13755282]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Strangers
Это просто идея или другой принцип решения задачки

Идея хорошая, да. Быстрее моего варианта в три раза (проверял на временной таблице с 700000 записей, мой вариант — 140370 ms, ваш — 45805 ms). Я подозревал, что таблица чисел тут была бы очень кстати, но не смог сходу реализовать :)
11 янв 13, 16:34    [13755425]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Гость333
Strangers
Это просто идея или другой принцип решения задачки

Идея хорошая, да. Быстрее моего варианта в три раза (проверял на временной таблице с 700000 записей, мой вариант — 140370 ms, ваш — 45805 ms). Я подозревал, что таблица чисел тут была бы очень кстати, но не смог сходу реализовать :)
Наша clr парсит 700к урлов де-то за 25-30 сек, но там идет полное разрезание и декодирование урла.
З.Ы. Дать не могу, к сожалению.
11 янв 13, 16:42    [13755475]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Гавриленко Сергей Алексеевич
Наша clr парсит 700к урлов де-то за 25-30 сек, но там идет полное разрезание и декодирование урла.
З.Ы. Дать не могу, к сожалению.

Ну понятно, что универсальный язык программирования лучше справится с этой задачей. Я примерно на это и намекал ТСу, говоря, что "лучше бы привлечь другие средства" :) Для работы я бы тоже написал clr, а вариант с T-SQL представил из любви к искусству :)

Если что, я гонял тесты на Core i5 @ 2.67GHz.
11 янв 13, 17:05    [13755622]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
hider
Member

Откуда:
Сообщений: 23
Спасибо всем большое за помощь. К сожалению надо именно средствами SQL.
14 янв 13, 15:11    [13767317]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
hider
надо именно средствами SQL

Можно узнать, откуда такое ограничение?
14 янв 13, 15:33    [13767493]     Ответить | Цитировать Сообщить модератору
 Re: Запрос: Выделение домена из ссылки.  [new]
hider
Member

Откуда:
Сообщений: 23
Гость333,
Потому что языков программирования не знаю, и только в T-SQL соображают более-менее.
14 янв 13, 16:32    [13767958]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить