Функция replace sql: REPLACE | SQL | SQL-tutorial.ru

Выгрузка данных из несвязанных таблиц в отдельные файлы с помощью python — NTA на vc.ru

NTA

В предыдущей статье мы рассмотрели, как с помощью Python можно создать и запустить SQL запрос с множеством условий для выгрузки информации из баз данных (БД) Oracle в один файл частями.

1756
просмотров

В данной статье мы расскажем, как использовать рассмотренный скрипт (частично изменив его) для выгрузки записей из различных несвязанных таблиц в отдельные файлы.

Не секрет, что прежде чем писать SQL запросы к таблицам БД для формирования выгрузок, необходимо проанализировать, какая информация в них содержится. Особенно это актуально в случае, если описание к БД отсутствует (что в нашей работе встречается нередко). Для этого, например, можно выгрузить небольшое количество записей из нужных таблиц в отдельные файлы excel. При этом зачастую таблиц, которые необходимо изучить, довольно много. Чтобы не выгружать информацию из каждой вручную, можно воспользоваться Python. Ниже мы рассмотрим, как это сделать на примере БД Oracle.

Для начала создадим txt файл с названием «dt_in.txt», в котором укажем таблицы для выгрузки данных в следующем виде:

Название_базы_данных_1.Название_таблицы_1;

Название_базы_данных_1.Название_таблицы_2;

Название_базы_данных_2.Название_таблицы_1;

Название_базы_данных_3.Название_таблицы_1

Обратите внимание, в файле не должно быть пробелов и пустых строк, в том числе после названия последней таблицы в списке. Указать можно таблицы разных БД.

Далее запускаем скрипт на Python (импорт библиотек, подключение к Oracle, описание функций для взаимодействия с Oracle и выполнения запроса можно найти в указанной выше статье).

#Имя файла со строками вида base_name. table_name;
fn = ‘dt_in.txt’
#Чтение входного файла с именами баз данных и таблиц
f = open(fn,’r’,encoding=’UTF-8′)
#Ключ для записи заголовков, если h=0, то происходит первый проход по циклу — записываем в файл заголовки,
#если h=1 — не первый проход по циклу, заголовки записаны
h=0
l = 0
#Счетчик строк для запуска основного SQL-запроса
for row in f:

dt = row.split(«;»)

#Имя файла в который выгружаем
new_file = str(dt[0].replace(‘\n’,»))
l += 1

#SQL-запрос (количество выгружаемых строк можно менять)
sql = «»»SELECT * FROM «»» + str(dt[0].replace(‘\n’,»)) + «»» where rownum<400″»»

#Проверка на наличие записей в таблице. Если записей в таблице нет, файл с ее именем не создается,
#дальнейшая ее выгрузка не производится
count= «»»SELECT count(*) FROM «»» + str(dt[0].replace(‘\n’,»)) + «»» where rownum<3″»»
with getOdsConn(odsLogin,odsConnectStr) as con1:
_header,result,us = dfFromOracle(con1, count)
if result. values[0,0]==0:
print(«Таблица » + new_file + » пустая»)
continue

#Выполнение основного SQL-запроса
with getOdsConn(odsLogin,odsConnectStr) as con1:
_header,result,us = dfFromOracle(con1, sql)

#Запись заголовков в выходной файл
if h==0:
header = ‘;’.join(_header)+’\n’
myfile=open(new_file + ‘.csv’, ‘w’,encoding=’UTF-8′)
myfile.writelines(header)
myfile.close()
h=1

#Запись выгруженных строк таблиц в файлы
result.to_csv(new_file + ‘.csv’, sep=’;’,encoding=’UTF-8′,mode=’a’,header=None)
print(‘Выгружено ‘ + str(len(result)) + ‘ из ‘ + str(new_file) )

f.close()

В результате, данные из таблиц автоматически выгружаются в отдельные файлы excel, названия которых содержат наименование БД и таблицы. Если таблица пустая – файл не формируется. В Python выводится сообщение о том, сколько записей выгружено из каждой таблицы, или, что она пустая.

Таким образом, с помощью Python можно:

— уменьшить трудозатраты на выгрузку информации для анализа из несвязанных таблиц БД в отдельные файлы;

— выгружать данные для анализа сразу из нескольких БД.

Написание табличной функции на PL/pgSQL — функция, которая возвращает таблицу | Info-Comp.ru

Продолжаем осваивать PL/pgSQL и сегодня мы рассмотрим примеры того, как можно написать функцию, которая возвращала бы не одно значение, а целую таблицу, то есть, табличные функции. Вам это может потребоваться, например, тогда, когда Вам нужно передавать в функцию параметры, выполнять какие-то расчеты и при этом нужно чтобы она вернула нам целую таблицу, так сказать уже рассчитанную, к которой в свою очередь, также можно обращаться и писать сложные запросы с различными объединениями.

Все примеры мы будем писать в СУБД PostgreSQL. Материал в данной статье подразумевает, что Вы уже знаете основы написания функций в PostgreSql, если нет, то для начала Вам необходимо ознакомиться с основами, в этом Вам поможет вот этот урок — Как написать функцию на PL/pgSQL.

Начнем с самого простого примера, в котором мы просто напишем функцию и передадим ей параметр, в результате выполнения она вернет нам таблицу, учитывая наш параметр.

Допустим, у нас есть вот такая таблица:

idnumbernameznach
1111mike10
2222peter20
3333eric10

Содержание

  1. Создание табличной функции на SQL в PostgreSQL
  2. Создание табличной функции на PL/pgSQL
  3. Создание табличной функции на PL/pgSQL с использованием курсора

Создание табличной функции на SQL в PostgreSQL

Давайте представим, что нам нужно выбрать всех с тем значением, которое мы укажем в передаваемом параметре, допустим по полю znach. Функция будет выглядеть следующим образом:

   
   CREATE OR REPLACE FUNCTION work.fun_test (IN integer)
      RETURNS TABLE (id numeric, number numeric, name text, znach numeric) AS
   $BODY$ 
      select * from work. test where znach = $1 
   $BODY$
      LANGUAGE 'sql' VOLATILE
      COST 100
      ROWS 1000;
   ALTER FUNCTION work.fun_test (integer) OWNER TO postgres;

Ключевым моментом здесь является то, что мы указываем тип возвращаемого значения TABLE и перечисляем все поля, которые мы хотим получить с указанием их типа.

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

   
   select * from work.fun_test (10)

где, 10 и есть то значение параметра, по которому мы хотим сделать выборку.

Передавать параметров можно много, также можно в самом запросе на выборку использовать и другие функции.

В этом примере мы использовали язык SQL без использования различных приемов программирования, например, условий (if then else), циклов (for) и других.


Создание табличной функции на PL/pgSQL

Теперь давайте попробуем написать тот же самый пример, но уже на PL/pgSQL.

Функция будет выглядеть следующим образом:

   
   CREATE OR REPLACE FUNCTION work.fun_test_2 ()
     RETURNS TABLE(id numeric, number numeric, name text, znach numeric) AS
   $BODY$ 
    DECLARE
    rec RECORD;
   BEGIN
   FOR rec IN EXECUTE 'select * from work.test where znach = 10'
        LOOP
                id = rec.id;
                number =rec.number;
                name = rec.name;
                znach = rec.znach;
                RETURN next;
        END LOOP;
   END;
   $BODY$
     LANGUAGE 'plpgsql' VOLATILE
     COST 100
     ROWS 1000;
   ALTER FUNCTION work.fun_test_2 () OWNER TO postgres;

Здесь таблица возвращается с помощью цикла.

Но таким способом мы не можем передать параметр в запрос, приходиться писать статическое значение, но здесь мы можем уже использовать все возможности PL/pgSQL.

Вызывается она таким же способом, но без параметров, но параметры можно передавать для других действий в этой функции.

   
   select * from work.fun_test_2 ()

Создание табличной функции на PL/pgSQL с использованием курсора

А если Вы все-таки хотите предавать параметры в запрос, это можно сделать с помощью курсоров.

Кстати, можно легко написать две функции, первая просто возвращала бы таблицу, с передачей параметров, а вторая, например, как в предыдущем примере, обрабатывала таблицу, которую вернет первая функция, и соответственно выполняла какие-нибудь другие действия, но вместо запроса, как в предыдущем примере, мы будем использовать cursor, кстати, познакомимся еще и с курсорами (если Вы не хотите писать две функции, вместо вызова первой функции в курсоре, напишите запрос). Функция будет выглядеть следующим образом:

   
   CREATE OR REPLACE FUNCTION work.fun_test_3 (IN numeric)
     RETURNS TABLE(id numeric, number numeric, name text, znach numeric) AS
   $BODY$ 
    DECLARE
     rec RECORD;
     var ALIAS FOR $1; 
     cur_test CURSOR FOR select * from work. fun_test (var)
   BEGIN
        FOR rec IN cur_test
                LOOP
                id = rec.id;
                number =rec.number;
                name = rec.name;
                znach = rec.znach;
                RETURN next;
        END LOOP;
        END;
   $BODY$
     LANGUAGE 'plpgsql' VOLATILE
     COST 100
     ROWS 1000;
   ALTER FUNCTION work.fun_test_3 (numeric) OWNER TO postgres;

Вызывается она также как и все предыдущие:

   
   select * from work.fun_test_3 (10)

Здесь мы с Вами объявили курсор и записали в него результат выполнения нашей функции select * from work.fun_test (var), а потом также циклом перебрали все значения. Помимо всего этого в теле цикла можно выполнять различного рода операции, доступные в PL/pgSQL, например, отработка условий if then else.

В итоге мы написали три разные функции, но результат их выполнения один и тот же. Теперь Вы знаете, как сделать так, чтобы функция могла возвращать таблицу, надеюсь, это вам поможет. Удачи!

Строковая функция SQL Server

REPLACE с примерами

В этом блоге мы изучим строковую функцию REPLACE. Мы будем использовать функцию REPLACE в операторах Select, а также операторы Update с примерами, чтобы лучше понять это.

Мы начнем с простого оператора Select, используя функцию Replace, чтобы понять, что он делает.

Проще говоря, функция REPLACE принимает строку в качестве первого параметра, а затем принимает шаблон в качестве второго параметра, ищет этот шаблон в первом параметре и заменяет тем, что передается в третьем параметре.

Давайте посмотрим на пример, чтобы лучше понять. См. приведенный ниже оператор выбора с функцией замены. Строковое значение «Easy very easy» передается в качестве первого параметра, затем передается второй параметр «EASY», который является строковым шаблоном, поэтому основная цель функции замены — найти этот шаблон в переданной строке и заменить его значением. «жесткий» везде, где он видит шаблон строки «EASY». Следовательно, он возвращает «жесткий, очень жесткий» в качестве вывода. Так как в строке есть 2 вхождения easy.. (см. 1-ю строку вывода)

 SELECT REPLACE('Просто очень просто','EASY','сложно')

Примечание:  Я выполнил все запросы сразу и зафиксировал результат, чтобы опубликовать результаты в виде одного снимка экрана.

В следующем примере мы увидим, что произойдет, если в качестве второго и (или) третьего параметра передать целые значения. Функция REPLACE по-прежнему будет возвращать выходные данные путем преобразования целочисленных значений в символьные или строковые значения. (см. 2-ю строку вывода)

 SELECT REPLACE('Скажем, 123',123,777)

Обратите внимание, что 123 не заключено в одинарные кавычки как «123», но функция все же преобразует его в строковое значение 123. Обратите внимание, это не означает, что вы передаете значения без одинарных кавычек.

Далее мы увидим, что произойдет, если один из параметров равен NULL. Если один из параметров имеет значение NULL, возвращаемый результат будет NULL. (см. 3-ю строку вывода) 

 SELECT REPLACE('В случае нулей',NULL,'Ничего')

Наконец, давайте посмотрим, что происходит, когда вы передаете один пробел в качестве шаблона поиска.

 SELECT REPLACE('Not NULL but Space',' ','BlankSpace')

Поскольку строковое значение ‘Not NULL but Space’ содержит 4 одиночных пробела, выходные данные будут формироваться путем замены этих 4 одиночных пробелов строкой значение «Пробел». (см. 4-ю строку вывода)

Далее давайте посмотрим, как использовать функцию REPLACE при обновлении данных таблицы. Прежде чем мы обновим данные таблицы, сначала давайте запустим этот запрос, чтобы увидеть, как выглядят данные.

 ВЫБЕРИТЕ ТОП 5 EmployeeID, LoginID
ОТ HumanResources.Employee
ЗАКАЗАТЬ ПО ID сотрудника

Ниже приведен снимок экрана при выполнении вышеуказанного запроса.

Теперь я хотел бы заменить значение « adventure-works » на « LearnSQLWthBru» во всех строках таблицы. Поэтому мы используем функцию «Заменить» в «установить имя столбца = выражение» в операторе обновления, как показано ниже….

 ОБНОВЛЕНИЕ AdventureWorks.HumanResources.Employee
SET LoginID = REPLACE(LoginID,'adventure-works','LearnSQLWithBru')

После выполнения этого запроса повторно запустите исходный запрос выбора для таблицы, чтобы увидеть, как данные выглядят после обновления. как это выглядит..

Предложение: При обновлении таблицы лучше запустить оператор select и включить предложение where (которое будет использоваться в операторе Update), чтобы убедиться, что вы собираетесь обновить как можно больше записей. он возвращается в операторе select.. 

Вам нравится этот сайт? Поставьте лайк нашей странице FB @ Facebook.com\LearnSQLWithBru, чтобы вы знали, когда появится новая запись в блоге.

— Брю Медишетти

Stuff VS Функция замены в SQL Server

Опубликовано 25 ноября 2013 г. автором vivekjohari

Stuff Function

Эта функция используется для замены строки с заданной начальной позиции, переданной в качестве второго аргумента, строкой, переданной в качестве последнего аргумента. В функции Stuff третий аргумент определяет количество символов, которые будут заменены.

Синтаксис: —

STUFF (знаковое_выражение, начало, длина, заменить с_выражением)

Например: —

Select Stuff («Программное обеспечение», 3, 3, «abc»)

Этот запрос вернет строку « Soabcare» . В этом примере функция Stuff заменяет строку « Software » до 3-й позиции («ftw») на «abc».

Функция замены 

Функция замены используется для замены всех вхождений указанного строкой, переданной в качестве последнего аргумента.

Синтаксис: —

REPLACE (строковое_выражение, строковое_шаблон, строковое_замещение)

Например:-

Выберите Заменить («Abcabcabc», «bc», «xy»)

Этот запрос вернет строку Axyaxyaxy . В этом примере функция замены заменяет вхождение каждой строки « bc » на « xy ».

Нравится:

Нравится Загрузка…

О вивекджохари

Консультант по базам данных с более чем 11,5-летним опытом проектирования и программирования баз данных, а также деятельности, связанной с администрированием баз данных. Имеет хороший опыт работы с различными базами данных, такими как SQL Server, MySQL и Oracle, Azure SQL и большие данные.

Просмотреть все сообщения от vivekjohari →

Эта запись была опубликована в Базовые понятия SQL, SQL Server и помечена как Функции SQL. Добавьте постоянную ссылку в закладки.

  • Поиск:

  • Следуйте за нами

  • Категории

    • Лазурь (8)
      • Восстановление базы данных Azure (1)
      • Миграция в базу данных SQL Azure (3)
    • ДБА (17)
    • Праздничные поздравления (1)
    • Вопросы для интервью (29)
    • Разное (10)
    • SQL-сервер (99)
      • Имп SQL Разница (6)
      • Большинство концепций Imp SQL (13)
      • Правило ACID SQL (1)
      • Расширенный SQL (17)
      • Базовый SQL (10)
      • Основные понятия SQL (17)
      • Рекомендации по SQL (2)
      • SQL CTE (2)
      • Курсор SQL (1)
      • типов данных SQL (1)
      • Резервное копирование базы данных SQL (1)
      • Зеркальное отображение базы данных SQL (1)
      • Восстановление базы данных SQL (3)
      • SQL DDL, DML, DCL, TCL (1)
      • Производная таблица SQL (1)
      • SQL-функции (17)
      • Группа SQL по наличию (1)
      • SQL-индексы (7)
      • Уровень изоляции SQL (1)
      • SQL-соединений (1)
      • Доставка журнала SQL (1)
      • Команда слияния SQL (1)
      • Нормализация SQL (5)
      • Пункт вывода SQL (1)
      • Сводная и несводная таблицы SQL (1)
      • Ограничение первичного ключа SQL (2)
      • SQL-запросов Подзапросы (1)
      • Функция ранжирования SQL (1)
      • SQL-репликация (1)
      • Обработка исключений SQL Server (1)
      • Хранимая процедура SQL (4)
      • Временные таблицы SQL (3)
      • триггеров SQL (3)
      • Функция объединения SQL (1)
      • Представление SQL (1)
      • SQL цикл «пока» (1)
    • Советы по SQL (5)

  • Поиск:

  • Последние сообщения

    • SQL присоединяется к каверзным вопросам интервью
      17 апреля 2019 г.
    • Восстановление на момент времени для базы данных SQL Azure
      8 января 2019 г.
    • Azure — создание базы данных Azure SQL
      27 декабря 2018 г.
    • С Рождеством
      24 декабря 2018 г.
    • Триггеры SQL — введение
      8 декабря 2018 г.
    • Миграция базы данных SQL в экземпляр Azure SQL с помощью средства Microsoft Database Migration Assistant (DMA)
      25 ноября 2018 г.
    • Как перенести базу данных SQL в базу данных SQL Azure с помощью экспорта/импорта SSMS
      19 ноября 2018 г.

  • Архивы

    • Апрель 2019 (1)
    • Январь 2019 (1)
    • Декабрь 2018 (3)
    • ноябрь 2018 (6)
    • Октябрь 2018 (1)
    • Апрель 2018 (1)
    • март 2018 г. (4)
    • август 2017 г. (1)
    • июль 2017 (1)
    • Сентябрь 2016 (1)
    • Декабрь 2015 (1)
    • август 2015 г. (3)
    • июль 2015 (3)
    • Февраль 2015 (1)

      • 901 35 января 2015 (16)

    • ноябрь 2014 г. (1)
    • Октябрь 2014 г.