Тег: t-SQL

transact SQL

Создание пользовательской функции с помощью CLR.Net (С#) в MS SQL Server.

Тут я показывал как можно определять сколько раз подстрока встречается в строке. Для этого мы создали обычную пользователскую функцию с помощью T-SQL.
Сейчас я покажу как сделать то же самое с помощью функции написанной с применением технологии SQLCLR.
1) Для начала нам потребуется создать в среде Visual Studio проект с типом Class Library:

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace CLRFunctions
{
    public static class CLRFunctions
    {
        [SqlFunction(
            DataAccess = DataAccessKind.None
            , IsDeterministic = true)
        ]
        public static SqlInt32 substring_cnt(string where, string what)
        {
            int i = 0;
            i = (where.Length - where.Replace(what, string.Empty).Length) / what.Length;
            return (SqlInt32)i;
        }
    }
}

Далее потребуется скомпилировать проект и получить библиотеку CLRFunctions.dll. Этот файл следует поместить в каталог, доступный для SQL Server.

2) Далее для того, чтобы можно было использовать библиотеки CLR, необходимо разрешить использование CLR на вашем сервере MS SQL:

sp_configure 'clr enabled', 1
reconfigure
go

3) Теперь можно создать Assembly на сервере:

create assembly CLRAssembly from 'c:\CLR\CLRFunctions.dll' with permission_set = safe
go

и пользовательскую функцию:

create function dbo.substring_cnt(@where nvarchar(1000), @what nvarchar(100))
returns int
as
	external name CLRAssembly.[CLRFunctions.CLRFunctions].substring_cnt

Применение функции:
select	dbo.substring_cnt('Hello, World!', 'o')

Количество вхождений подстроки в строку в SQL

В некоторых задачах SQL бывает необходимо подсчитать количество вхождений подстроки в строку. Самое простое решение в таком случае сводится к следующему алгоритму (строка, в которой ведется поиск – A, подстрока – B):

  1. Определить длину строки A.
  2. Заменить в строке A все подстроки B на пустые подстроки.
  3. Определить длину строки, полученной во втором шаге.
  4. Из длины, полученной на первом шаге вычесть длину, полученный в третьем шаге.
  5. Результат, полученный в четвертом шаге, поделить на длину подстроки B.

Оформим приведенный алгоритм в t-SQL в виде функции с двумя параметрами, где переменная @where – это строка A, а @what – подстрока B:

create function [dbo].[substring_cnt]
    (@where varchar(max), @what varchar(100))
returns int
as
begin
    return ((datalength(@where) - datalength(replace(@where, @what, ''))) / datalength(@what))
end

.

В своей следующей записи я покажу как реализовать приведенную в этой статье функцию в виде CLR-функции.

Последнее вхождение подстроки в строку – last charindex

Всем известно про функцию t-sql charindex, которая возвращает начальную позицию подстроки в строке. Но некоторых случаях необходимо бывает найти начальную позицию не первого вхождения подстроки, а последнего. Для этого я написал пользовательскую функцию last_charindex. Ниже приведу ее код:

create function [dbo].[last_charindex]
	(@what varchar(1000), @where varchar(1000))
returns int
as
begin
	set @where = reverse(@where)
	set @what = reverse(@what)

	return (case
			when charindex(@what, @where) > 0
			then len(@where) - (len(@what) + charindex(@what, @where)) + 2
			else 0
	end)
end

Полный возраст в MS SQL.

В MS SQL Server есть очень удобная и замечательная встроенная функция datediff, которая возвращает разницу между двумя датами. Например, следующий запрос вернет разницу в годах между 31-м декабря 2014 года и 1-м января 2015 года:

select datediff(year, '2014-12-31', '2015-01-01')

Результат запроса будет равен 1, хотя разница между двумя всего 1 день. Дело в том, что datediff возвращает количество пересеченных границ указанных аргументом datepart, за период времени между аргументами startdate и enddate функции.
Такой результат не очень устраивает нас, если мы хотим определить количество полных лет между двумя датами, т.е. возраст на определенную дату. Немного дополним вывод этой функции и оформим в виде функции:

create function [dbo].fullAge
(@startdate datetime
, @enddate datetime)
returns int
as
begin
return	(datediff(year, @startdate, @enddate) -
		case
			when month(@startdate) < month(@enddate)
			then 0
			when month(@startdate) > month(@enddate)
			then 1
			when day(@startdate) > day(@enddate)
			then 1
			else 0
		end)
end

Здесь вначале находится разница в годах между двумя календарными датами функцией datediff и от неё вычитается 1, если в календарном году даты @enddate месяц и дата переменной @startdate еще не наступили относительно переменной @enddate.

Високосный год в SQL.

По григорианскому календарю год считается високосным если он кратен 4, но при этом не кратен 100, либо кратен 400. С учетом этого можем написать функцию для определения “високосности” года.

create function [dbo].[isLeapYear]
(@date date)
returns bit
as
begin
return (case
			when year(@date) % 4 = 0 and year(@date) % 100 != 0
			then 1
			when year(@date) % 400 = 0
			then 1
			else 0
		end)
end

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

Пример использования функции isLeapYear