Количество вхождений подстроки в строку в 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-функции.

Первое число месяца в Excel.

Я уже тут писал о том, как получить первое число месяца в Transact-SQL. Сейчас напишу как это сделать в Excel’e.
Существуют много способов сделать это. Мне нравятся два из них:
1) От исходной даты вычитаем номер дня в месяце – получаем последний день месяца предшествующий месяцу исходной даты. К этому результату прибавляем один день. Эта дата и будет нашим результатом. Формула для этого способа: “=A1-ДЕНЬ(A1)+1″.
=A1-ДЕНЬ(A1)+1

2) С помощью функции ДАТА(год;месяц;день) и из составляющих исходной даты (год и месяц) получаем первое число: “=ДАТА(ГОД(A1);МЕСЯЦ(A1);1)”.
=ДАТА(ГОД(A1);МЕСЯЦ(A1);1)

Последнее вхождение подстроки в строку – 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