Процесс извлечения URL-адреса переменной sql substr
Контекст:
до сих пор я использовал регулярное выражение в sql для извлечения URL-адресов переменных. Я считаю это очень медленным и хочу оптимизировать его с помощью команд substr и instr. Это важно для меня, потому что, поскольку я новичок в sql, я лучше знаком с такими командами.
база данных: моя база данных создается сообщениями, извлеченными с социальных платформ. текст называется «титр». Он содержит url переменных в разных форматах: www, http, https. Я хочу создать таблицу или табличное представление (я не зафиксировано), содержащее этот URL-адрес и связанный с ним id_post.
Моя работа: Я заметил, что url всегда заканчивается пробелом, например: "toto хочу поделиться с вами этим www.example.com в своем сообщении" вот что я сделал до сих пор:
---longueur de la chaîne de caractère depuis https
select LENGTH(substr(titre, INSTR(titre,'https:'))) from post_categorised_pages where id_post = '280853248721200_697941320345722';
---longueur de la chaîne de caractère depuis le blanc
select LENGTH(substr(titre, INSTR(titre,' ', 171))) from post_categorised_pages where id_post = '280853248721200_697941320345722';
--- différence pour obtenir la longueur de chaîne de caractères de l'url
select LENGTH(substr(titre, INSTR(titre,'https:'))) - LENGTH(substr(titre, INSTR(titre,' ', 171))) as longueur_url from post_categorised_pages where id_post = '280853248721200_697941320345722';
---url
select substr(titre, 171, 54)from post_categorised_pages where id_post = '280853248721200_697941320345722';
Вопрос: Как я могу автоматизировать это по всей таблице post_categorised_page? Могу ли я ввести случай, когда в выписках нужно учитывать https или http из www. и как я могу это сделать?
Большое спасибо!!!!
Хорошо ... второй комментарий, этот материал работает очень хорошо, когда вы встречаете только 1 URL-адрес в строке. Как я могу использовать connect by и level, чтобы перебирать строку и получать все URL-адреса?
Ваш запрос немного сложно понять. вы можете опубликовать пример, пожалуйста? Было бы полезно опубликовать ваш фактический оператор выбора, вашу примерную строку и то, что вы хотите получить.
хорошо, мик. возьмем эту строку: «Primeiramente #ForaTemer. #ForaTodos! Ninguém mais quer ser amigo do Eduardo Cunha». я хочу извлечь все хэштеги. Если я использую свое решение (выберите substr (titre, INSTR (titre, '#'), (LENGTH (substr (titre, INSTR (titre, '#'))) - LENGTH (substr (titre, INSTR (titre, '' , (INSTR (titre, '#'))))))) from post_categorised_pages, где id_post = '280853248721200_788642914608895'; ") я буду извлекать только первый #. В python мы могли бы перебирать строку, чтобы перехватить все #. В SQL i Не знаю, что делать. Я слышал о решениях для подключения по уровням, но не могу понять, как это работает.
вот начало решения, чтобы получить всю позицию конкретной строки, которую я ищу: с сообщением как (выберите заголовок из post_categorised_pages, где id_post = '521551221207650_653223808040390') выберите instr (titre, '#', 1, level) из пост-соединение по уровню <= regexp_count (titre, '#');
Итак, согласно приведенному выше комментарию, запрос дает мне всю позицию определенного шаблона в данной строке. Что я хочу сделать сейчас, так это получить эти позиции и искать для каждой позицию следующего пустого места (''). Если я могу это сделать, следующим шагом будет извлечение строки между каждой позицией pattern1 и каждым следующим пустым пространством. Прозрачный?
Ответы 3
Возможно, вместо строки «HTTP», «HTTPS» или «WWW» вам понадобится имя столбца. В этом случае, вероятно, было бы полезно иметь таблицу определений, в которой можно определить все возможные источники. В этой таблице должно быть 2 столбца (ID и имя_источника).
Затем в вашу таблицу post_categorised_pages, чтобы вставить также источник сообщения (значение ID).
Затем в запрос, чтобы присоединиться к этой таблице определений по идентификатору и вместо
select substr(titre, INSTR(titre,'https:'), (LENGTH(substr(titre, INSTR(titre,'https:'))) - LENGTH(substr(titre, INSTR(titre,' ', (INSTR(titre,'https:')))))))from post_categorised_pages where id_post = '280853248721200_697941320345722';
иметь
select substr(titre, INSTR(titre,"definition table".source_name), (LENGTH(substr(titre, INSTR(titre,"definition table".source_name))) - LENGTH(substr(titre, INSTR(titre,' ', (INSTR(titre,"definition table".source_name)))))))from post_categorised_pages where id_post = '280853248721200_697941320345722';
хорошо .... ну, я не понимаю, что ты имеешь в виду. я пойду, чтобы взглянуть на «таблицу определений», это функция sql?
Нет, это не функция. Это должна быть таблица, созданная вами, что-то вроде CREATE TABLE source (ID number, SOURCE_NAME varchar2 (10)); ВСТАВИТЬ В источник (1, 'HTTP'); ВСТАВИТЬ В исходные ЗНАЧЕНИЯ (2, 'HTTPS'); ВСТАВИТЬ В исходные ЗНАЧЕНИЯ (3, 'WWW');
Или вы можете посмотреть здесь: stackoverflow.com/questions/17906105/…
ахахахах оки да я вижу! спасибо за уловки. Думаю, я бы сделал это, но только с одной командой sql, собирающей в одной таблице http, https и источник www. Теперь у меня большие проблемы с выполнением цикла запроса по строке, чтобы получить все html / #, упомянутые в этой же строке, как я объяснил выше.
Я пытаюсь найти решение, указанное в вашей ссылке, но для подсчета требуется много времени ...
Также есть решение с функцией PL SQL. Вы можете создать свою собственную функцию для возврата позиции хэштега и позиции следующего пробела. Затем с помощью этой пары вы можете получить шнурок.
о да, я показал это, но я не могу программировать pl / sql, иначе это будет мой самый первый раз ^^
Хорошо, ребята, вот решение, которое я нашел (есть еще одна ошибка, см. В конце сообщения). Я использую два представления, чтобы наконец извлечь свои строки. Первое представление создается подключением по запросу:
--- create intermediate table view with targeted pattern position
create or replace view Start_Position_Index as
with "post" as
(select id, text from "your_table" where id= 'xyz')
select id, instr(text,'#', 1, level) as position, text
from post
connect by level <= regexp_count(titre, '#');
тогда
--- create working table view with full references and blank position for each pattern match and string_lenght for each one
create or replace view _#_index as
select id, position as hashtag_pos, INSTR(text,' ', position) as blank_position, INSTR(text,' ', position) - position as string_length, text
from Start_Position_Index;
В конце вы сможете получить хэштеги (в этом случае), которые вы искали в своей строке. Итак, ошибки: - если шаблон, который вы ищете, находится в конце вашей строки, он получит нулевое значение, потому что не будет пробелов (как в конце строки). - это не очень хорошо оптимизировано, потому что здесь я работаю с представлениями, а не с таблицами. Думаю, пользоваться таблицами будет быстрее.
Но я почти уверен, что есть много вещей, которые нужно сделать, чтобы оптимизировать этот код ... есть идеи? Проблема заключалась в том, как рекурсивно извлекать конкретный шаблон среди строк без использования дорогостоящего регулярного выражения и без использования материалов pl / sql. Что вы думаете об этом?
Как насчет использования поиска Полный текст Oracle?
Это проиндексирует все слова из столбца и предоставит хэштеги или веб-адреса, поскольку оба они записаны одним словом без пробелов между ними.
Привет, mikcutu, извини, что ответил так в последнее время. Но да, вы правы, текст оракула очень мощный, и я тоже его тестировал. Работает нормально. Думаю, я приведу здесь несколько примеров, когда закончу анализ ^^ Спасибо за реакцию
и здесь стоит версия с одним вкладышем: --- версия с одним вкладышем select substr (titre, INSTR (titre, 'https:'), (LENGTH (substr (titre, INSTR (titre, 'https:'))) - LENGTH ( substr (titre, INSTR (titre, '', (INSTR (titre, 'https:'))))))) из post_categorised_pages, где id_post = '280853248721200_697941320345722';