MS SQL. Выборка данных без учета диакритических знаков

Встала задача находить записи в базе данных, которые содержали диакритические знаки без указания этих знаков. Например, если представить, что в базе находится фирма “Télécöm”, хотелось бы находить ее по значению “Telecom”. В результаты наших запросов эти значения не попадали.

Проблема таилась в сортировке, которая использовалась в базе данных по умолчанию: Latin1_General_ci_AS. Эта сортировка с суффиксом “_AS” различает символы с диакритическими знаками, так что 'a' и 'ấ' это не одно и то же. Сортировка с суффиксом “_AI”, ей в противоположность, считает эти символы идентичными. Подробнее о сортировках можно почитать здесь.

Итак, проблему решили путем применения сортировки Latin1_General_ci_AI на выбираемые колонки при помощи оператора COLLATE:

SELECT ID, Company
FROM Companies
WHERE (Company LIKE N'Telecom' COLLATE Latin1_General_ci_AI)

В заключении хочу добавить, что сортировка вообще определяется MS SQL Server-ом на нескольких уровнях: от уровня сервера, через уровень базы, затем через уровень столбца, до уровня выражений. Мы применили уровень выражения, но можно было бы, конечно, использовать и другие уровни.

Ранкинг (Ranking). SQL запросы с рангом (Rank).

Ранкинг (Ranking) - это предоставление информации о соответствии найденной записи определенному запросу. Это соответствие в простейшем случае можно отобразить в процентном соостветсвии. Т.е. записи удовлетворяющие всем критериям имеют 100%-ный ранкинг.

Реализация ранкинга может показаться достаточно сложной задачей. Но это только так кажется. SQL может взять эту задачу на себя! Для примера я использовал MySQL, реально использовал MSSQL.

Итак, таблица:

CREATE TABLE `test_db`.`person` (
`id` INT NOT NULL,
`name` VARCHAR (100),
`age` int ,
PRIMARY KEY (`id`)
)

Наполним данными:

INSERT INTO test_db.person VALUES (1,'Ralf', 20),(2,'Peter', 30),(3,'Patrick', 40);

Найдем записи, которые соответсвуют одному из трех критериев:
- все старше 25;
- все младше 35;
- имена начинаются на "P";

Запрос может выглядеть так:

SELECT p.id, p.name, p.age FROM test_db.person p
WHERE age > 25 OR age < 35 OR name Like 'P%'

А результат запроса, так:
id, name, age
------------
1,'Ralf', 20
2,'Peter', 30
3,'Patrick',40

Добавим ранкинг (ranking). Объяснять не буду, все очень просто:

SELECT p.id, p.name, p.age, (100/3)*under1.hit + (100/3)*under2.hit + (100/3)*under3.hit AS rank FROM test_db.person p
LEFT JOIN (Select id, 1 As hit from test_db.person WHERE age > 25) AS under1 On under1.id = p.id
LEFT JOIN (Select id, 1 As hit from test_db.person WHERE age < 35) AS under2 On under2.id = p.id LEFT JOIN (Select id, 1 As hit from test_db.person WHERE name like 'p%') AS under3 On under3.id = p.id WHERE age > 25 OR age < 35 OR name like 'p%'

Результат:
id, name, age, rank
------------
1,'Ralf', 20, null
2,'Peter', 30, null
3,'Patrick', 40, null

Почти то, что нужно, но есть проблема с приведением арфиметического результа к null:
Для этого в MySql можно использовать функцию IFNULL (в MSSQL это функция ISNULL):

SELECT p.id, p.name, p.age, (100/3)*IFNULL(under1.hit, 0) + (100/3)*IFNULL(under2.hit,0) + (100/3)*IFNULL(under3.hit,0) AS rank FROM test_db.person p
LEFT JOIN (Select id, 1 As hit from test_db.person WHERE age > 25) AS under1 On under1.id = p.id
LEFT JOIN (Select id, 1 As hit from test_db.person WHERE age < 35) AS under2 On under2.id = p.id LEFT JOIN (Select id, 1 As hit from test_db.person WHERE name like 'p%') AS under3 On under3.id = p.id WHERE age > 25 OR age < 35 OR name like 'p%'

Результат:
id, name, age, rank
------------
1,'Ralf', 20, 33.333
2,'Peter', 30, 100.000
3,'Patrick', 40, 66.666

MS SQL. Замена NULL на другие значения

Встретился с небольшой проблемкой, когда в математическом выражении один из операндов является NULL, то результат выражения становится нуль. Вот нашел на просторах интернета по этому поводу следующую информацию. А мне нужно было интерпретировать NULL в 0. Для этого, к счастью, есть в MS SQL функция:
ISNULL ( check_expression , replacement_value )

Например:
SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max Quantity'
FROM Sales.SpecialOffer;

Заполнение параметров значениями для SQL-выражения

Понадобилось протестировать созданное динамически SQL-выражение с параметрами, типа

Select * From Person
Where ID = @param

Чтобы выполнить такой запрос в окне запросов SQL Server Management Studio, необходимо объявить переменные-параметры и заполнить их значениями. Делается это так:

Declare @param uniqueidentifier
Set @param = '5792c959-33b4-47eb-a824-b74d32295506'

Также удалось использовать параметры следующих типов:

Строка:
DECLARE @param nvarchar(255)
SET @param = 'Dr%'

Целое число:
DECLARE @param int
SET @param = 123

Дата-время:
Declare @param DateTime
Set @param = '01/12/2010 14:02:17'