Главная страница
Навигация по странице:

  • Функция DATEFROMPARTS Функция DATEFROMPARTS

  • 2017-05-13 09:38:59.997 2017-05-13 09:39:00.000 2017-05-13 09:38:59.993 Функции работы со строками в MS SQL SERVER

  • ASCII NCHAR SOUNDEX CHAR PATINDEX SPACE CHARINDEX REPLACE STR DIFFERENCE

  • ASCII и CHAR : Функция ASCII

  • CHARINDEX и PATINDEX

  • PATINDEX

    		•

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


    Скачать 7.88 Mb.
    НазваниеУчебник по sql если вы хотите узнать, что такое sql этот сайт для вас
    Анкорлекция
    Дата21.12.2022
    Размер7.88 Mb.
    Формат файлаpdf
    Имя файлаMoiseenko_SQL.pdf
    ТипУчебник
    #857633
    страница26 из 47
    1   ...   22   23   24   25   26   27   28   29   ...   47
    Первый
    день
    недели
    Задача. Определить дату, на которую выпал первый
    понедельник января 2013 года.
    При некоторых предположениях решить эту задачу можно следующим образом:
    1.
    WITH
    num
    (
    n
    )
    AS
    (
    /* с помощью рекурсивного CTE создаем
    таблицу со столбцом n
    2.
    и значениями от 0 до 6 */
    3.
    SELECT
    0 4.
    UNION
    ALL
    5.
    SELECT
    n
    +1
    FROM
    num
    6.
    WHERE
    n <
    6
    )
    ,
    7. dat
    AS
    (
    /* создаем таблицу с датами от 1 до 7 января 2013
    года */
    8.
    SELECT
    DATEADD
    (
    dd, n,
    CAST
    (
    '2013-01-01'
    AS
    DATETIME
    ))
    AS
    day
    FROM
    num
    9.
    )
    10.
    SELECT
    day
    FROM
    dat
    WHERE
    DATEPART
    (
    dw, day
    )
    =
    1
    ;
    /* выбираем день, соответствующий
    11.
    первому дню недели */
    Предположение, о котором говорилось выше, состоит в том, что первым днем недели считается понедельник. Однако если вы выполните этот запрос на сайте sql-ex.ru, то получите
    day
    2013-01-06
    А это - воскресенье. Причина в том, что настройки на сайте полагают первым днем недели воскресенье. А можно ли написать решение, которое не зависело бы от настроек сервера? Попробуем сделать так: будем не номер дня использовать при фильтрации, а его название (поменяется лишь последняя строка, но я повторяю весь запрос с тем, чтобы его можно было выполнить без редактирования):

    1.
    WITH
    num
    (
    n
    )
    AS
    (
    SELECT
    0 2.
    UNION
    ALL
    3.
    SELECT
    n
    +1
    FROM
    num
    4.
    WHERE
    n <
    6
    )
    ,
    5. dat
    AS
    (
    6.
    SELECT
    DATEADD
    (
    dd, n,
    CAST
    (
    '2013-01-01'
    AS
    DATETIME
    ))
    AS
    day
    FROM
    num
    7.
    )
    8.
    SELECT
    day
    FROM
    dat
    WHERE
    DATENAME
    (
    dw, day
    )
    =
    'monday'
    ;
    day
    2013-01-07
    Теперь правильно, но будет ли этот запрос всегда верен, если "зашить" его в код приложения? Ответ - нет. Если я поменяю языковые настройки:
    1.
    SET
    LANGUAGE
    russian; то получу пустой набор строк, поскольку в этом случае последнюю строку запроса следовало бы написать так
    1.
    SELECT
    day
    FROM
    dat
    WHERE
    DATENAME
    (
    dw, day
    )
    =
    N
    'понедельник'
    ;
    Функция @@DATEFIRST
    @@DATEFIRST возвращает число, которое определяет первый день недели, установленный для текущей сессии. При этом 1 соответствует понедельнику, а 7, соответственно, воскресенью. Т.е. если
    1.
    SELECT
    @@DATEFIRST;
    возвращает 7, то первым днем недели считается воскресенье (соответствует текущим настройкам на сайте).
    Для того, чтобы решение нашей задачи не зависело от значения, установленного для первого дня недели, воспользуемся функцией
    @@DATEFIRST. Сделать это можно, например, так
    1.
    WITH
    num
    (
    n
    )
    AS
    (
    2.
    SELECT
    0 3.
    UNION
    ALL
    4.
    SELECT
    n
    +1
    FROM
    num
    5.
    WHERE
    n <
    6
    )
    ,
    6. dat
    AS
    (
    7.
    SELECT
    DATEADD
    (
    dd, n,
    CAST
    (
    '2013-01-01'
    AS
    DATETIME
    ))
    AS
    day
    FROM
    num
    8.
    )
    9.
    SELECT
    day, DATENAME
    (
    dw, day
    )
    week_day
    FROM
    dat
    WHERE
    DATEPART
    (
    dw, day
    )
    =
    10.
    1
    +
    (
    8
    -@@DATEFIRST
    )
    %
    7
    ;
    day
    week_day
    2013-01-07 Monday
    Чтобы изменить значение первого дня недели (на время текущей сессию), можно использовать оператор SET DATEFIRST. Так, если выполнить следующий код
    1.
    SET
    DATEFIRST
    1
    ;
    2.
    SELECT
    @@DATEFIRST; то мы получим значение 1, т.е. неделя теперь начинается с понедельника.
    Настройку первого дня недели может также поменять изменение языка сессии. Например, если мы выполним
    1.
    SET
    LANGUAGE
    us_english;
    2.
    SELECT
    @@DATEFIRST;
    то опять получим 7 (воскресенье), поскольку это значение DATEFIRST принято по умолчанию для английского (американского) языка. В то время как выбор русского языка (SET LANGUAGE russian;) сделает первым днем недели понедельник.
    Однако вы можете поменять параметры языка и первого дня недели одновременно, чтобы, скажем, получить английский язык, и при этом педеля будет начинаться с понедельника:
    1.
    SET
    DATEFIRST
    1
    ;
    2.
    SET
    LANGUAGE
    us_english;
    3.
    SELECT
    @@DATEFIRST;
    Языковые настройки влияют, в частности, на символьное представление компонентов даты/времени. Сравните, например, результаты:
    1. DECLARE @dt DATE =
    '2012-12-17'
    ;
    -- 17 декабря 2012 года
    2.
    SET
    LANGUAGE
    us_english;
    3.
    SELECT
    DATENAME
    (
    DW,
    @dt
    )
    AS
    day_of_week,
    DATENAME
    (
    MONTH,@dt
    )
    AS
    month;
    day_of_week
    month
    Monday
    December
    1.
    SET
    LANGUAGE
    russian;
    2.
    SELECT
    DATENAME
    (
    DW, @dt
    )
    AS
    day_of_week, DATENAME
    (
    MONTH,
    @dt
    )
    AS
    month;
    day_of_week
    month
    понедельник
    Декабрь
    1.
    SET
    LANGUAGE
    german;
    2.
    SELECT
    DATENAME
    (
    DW, @dt
    )
    AS
    day_of_week, DATENAME
    (
    MONTH,
    @dt
    )
    AS
    month;
    day_of_week
    month
    Montag
    Dezember

    Вывод. Если вы хотите, чтобы зависящие от настроек сервера/базы запросы всегда давали верный результат, можно задавать необходимые настроечные параметры для сессии, в которой эти запросы выполняются, или же проверять в каждом подобном запросе значения соответствующих параметров, в частности, с помощью функции @@DATEFIRST.
    Особенности: MSSQL
    Решить эту задачу (Определить дату, на которую выпал первый
    понедельник января 2013 года.) можно и без применения функции
    @@DATEFIRST, воспользовавшись методом, предложенным Ициком
    Бен-Ганом [
    8
    ]. Идея решения заключается в том, что:
    1. Первое января 1900г. было понедельником.
    2. Количество дней между двумя одинаковыми днями недели всегда
    кратно семи.
    1. declare @anchor_date datetime
    2. declare @reference_date datetime
    3.
    SELECT
    @anchor_date=
    '19000101'
    ,
    @reference_date=
    '20130505'
    4.
    SELECT
    DATEADD
    (
    day, DATEDIFF
    (
    day, @anchor_date,
    5. DATEADD
    (
    year,
    DATEDIFF
    (
    year,
    '19000101'
    ,
    @reference_date
    )
    ,
    '19000101'
    )
    -
    1
    )
    /
    7
    *
    7
    +
    7
    ,
    6. @anchor_date
    )
    ;
    Особенности: MSSQL
    Можно упомянуть еще один способ решения этой задачи без
    @@DATEFIRST, который использует сравнение любой из функций
    DATEPART/DATENAME с такой же функцией от даты, которая
    ЗАВЕДОМО
    является
    понедельником.
    Например,
    от
    уже
    упомянутого 1-го января 1900г.
    Тогда первоначальный запрос из этой главы выглядел бы так:
    1.
    SELECT
    day
    FROM
    dat
    WHERE
    DATEPART
    (
    dw, day
    )
    =
    DATEPART
    (
    dw,
    '19000101'
    )
    ; или так

    1.
    SELECT
    day
    FROM
    dat
    WHERE
    DATENAME
    (
    dw, day
    )
    =
    DATENAME
    (
    dw,
    '19000101'
    )
    ;
    Функция
    DATEFROMPARTS
    Функция DATEFROMPARTS появилась в
    SQL
    Server версии 2012.
    У функции
    DATEFROMPARTS
    3 целочисленных аргумента, представляющих собой год, месяц и день, а возвращаемое значение есть соответствующая дата типа
    DATE. Она даёт удобный способ формирования даты, когда её компоненты хранятся отдельно или передаются с клиента.
    Например,
    1.
    SELECT
    DATEFROMPARTS
    (
    2017
    ,
    5
    ,
    25
    )
    ; вернёт
    2017-05-25
    Получить дату 25 числа текущего месяца можно так
    1.
    SELECT
    DATEFROMPARTS
    (
    YEAR
    (
    CURRENT_TIMESTAMP
    )
    ,
    MONTH
    (
    CURRENT_TIMESTAMP
    )
    ,
    25
    )
    ;
    Помимо даты можно "собирать" значения типа времени и временной метки
    (datetime), используя компоненты времени - часы, минуты, секунды и доли секунды. Например, следующий запрос
    1.
    SELECT
    TIMEFROMPARTS
    (
    9
    ,
    38
    ,
    59
    ,
    998
    ,
    7
    )
    ; даст значение типа TIME:
    09:38:59.0000998

    Обратите внимание на последний параметр, который указывает на число знаков в представлении долей секунды. Этот целочисленный параметр может принимать значение от 0 до 7, но не может быть меньше числа цифр в представлении долей секунды.
    Например,
    1.
    SELECT
    TIMEFROMPARTS
    (
    9
    ,
    38
    ,
    59
    ,
    998
    ,
    3
    )
    ; вернет
    09:38:59.998
    в то время как
    1.
    SELECT
    TIMEFROMPARTS
    (
    9
    ,
    38
    ,
    59
    ,
    998
    ,
    2
    )
    ; вернет ошибку:
    Cannot construct data type time, some of the arguments have values which are not
    valid.
    (Не удалось сконструировать тип данных time. Некоторые аргументы имеют недопустимые значения.)
    Сравните с
    1.
    SELECT
    TIMEFROMPARTS
    (
    9
    ,
    38
    ,
    59
    ,
    098
    ,
    2
    )
    , TIMEFROMPARTS
    (
    9
    ,
    38
    ,
    59
    ,
    098
    ,
    3
    )
    ;
    Результат
    09:38:59.98 09:38:59.098
    Функция DATETIMEFROMPARTS формирует значение типа DATETIME.
    Она имет семь целочисленных параметров: год, месяц, день, часы, минуты, секунды, миллисекунды. При этом миллисекунды округляются с точностью до одного из значений: .000, .003, .007.
    1.
    SELECT
    DATETIMEFROMPARTS
    (
    2017
    ,
    5
    ,
    13
    ,
    9
    ,
    38
    ,
    59
    ,
    998
    )
    ,
    2. DATETIMEFROMPARTS
    (
    2017
    ,
    5
    ,
    13
    ,
    9
    ,
    38
    ,
    59
    ,
    999
    )
    ,
    3. DATETIMEFROMPARTS
    (
    2017
    ,
    5
    ,
    13
    ,
    9
    ,
    38
    ,
    59
    ,
    993
    )
    ;
    Результат

    2017-05-13
    09:38:59.997
    2017-05-13 09:39:00.000 2017-05-13 09:38:59.993
    Функции
    работы со
    строками в
    MS SQL
    SERVER
    Вот полный перечень функций работы со строками, взятый из BOL:
    ASCII
    NCHAR
    SOUNDEX
    CHAR
    PATINDEX
    SPACE
    CHARINDEX
    REPLACE
    STR
    DIFFERENCE QUOTENAME STUFF
    LEFT
    REPLICATE
    SUBSTRING
    LEN
    REVERSE
    UNICODE
    LOWER
    RIGHT
    UPPER
    LTRIM
    RTRIM
    Функции
    ASCII и
    CHAR
    Начнем с двух взаимно-обратных функций
    — ASCII и CHAR:
    Функция ASCII возвращает ASCII-код крайнего левого символа строкового выражения, являющегося аргументом функции.

    Вот, например, как можно определить, сколько имеется разных букв, с которых начинаются названия кораблей в таблице Ships:
    1.
    SELECT
    COUNT
    (
    DISTINCT
    ASCII
    (
    name
    ))
    2.
    FROM
    Ships;
    Результат — 11. Чтобы выяснить, какие это буквы, мы можем применить функцию CHAR, которая возвращает символ по известному ASCII-коду (от 0 до 255):
    1.
    SELECT
    DISTINCT
    CHAR
    (
    ASCII
    (
    name
    ))
    2.
    FROM
    Ships
    3.
    ORDER
    BY
    1
    ;
    Следует отметить, что аналогичный результат можно получить проще с помощью еще одной функции — LEFT.
    Функция
    LEFT
    Функция имеет следующий синтаксис:
    1.
    LEFT
    (
    строковое выражение, целочисленное выражение
    )
    и вырезает заданное вторым аргументом число символов слева из строки, являющейся первым аргументом. Итак,
    1.
    SELECT
    DISTINCT
    LEFT
    (
    name,
    1
    )
    2.
    FROM
    Ships
    3.
    ORDER
    BY
    1
    ;

    А вот как, например, можно получить таблицу кодов всех алфавитных символов:
    1.
    SELECT
    CHAR
    (
    ASCII
    (
    'a'
    )
    + num
    -1
    )
    letter, ASCII
    (
    'a'
    )
    + num -
    1
    [
    code
    ]
    2.
    FROM
    (
    SELECT
    5
    *
    5
    *
    (
    a
    -1
    )
    +5
    *
    (
    b
    -1
    )
    + c
    AS
    num
    3.
    FROM
    (
    SELECT
    1
    a
    UNION
    ALL
    SELECT
    2
    UNION
    ALL
    SELECT
    3 4.
    UNION
    ALL
    SELECT
    4
    UNION
    ALL
    SELECT
    5 5.
    )
    x
    CROSS
    JOIN
    6.
    (
    SELECT
    1
    b
    UNION
    ALL
    SELECT
    2
    UNION
    ALL
    SELECT
    3 7.
    UNION
    ALL
    SELECT
    4
    UNION
    ALL
    SELECT
    5 8.
    )
    y
    CROSS
    JOIN
    9.
    (
    SELECT
    1
    c
    UNION
    ALL
    SELECT
    2
    UNION
    ALL
    SELECT
    3 10.
    UNION
    ALL
    SELECT
    4
    UNION
    ALL
    SELECT
    5 11.
    )
    z
    12.
    )
    x
    13.
    WHERE
    ASCII
    (
    'a'
    )
    + num
    -1
    BETWEEN
    ASCII
    (
    'a'
    )
    AND
    ASCII
    (
    'z'
    )
    ;
    Здесь используется алгоритм генерации числовой последовательности
    , изложенный в главе 8.
    Как известно, коды строчных и прописных букв отличаются. Поэтому, чтобы получить полный набор без переписывания запроса, достаточно просто дописать к вышеприведенному коду аналогичный:
    1.
    UNION
    2.
    SELECT
    CHAR
    (
    ASCII
    (
    'A'
    )
    + num
    -1
    )
    letter, ASCII
    (
    'A'
    )
    + num -
    1
    [
    code
    ]
    3.
    FROM
    (
    SELECT
    5
    *
    5
    *
    (
    a
    -1
    )
    +5
    *
    (
    b
    -1
    )
    + c
    AS
    num
    4.
    FROM
    (
    SELECT
    1
    a
    UNION
    ALL
    SELECT
    2
    UNION
    ALL
    SELECT
    3 5.
    UNION
    ALL
    SELECT
    4
    UNION
    ALL
    SELECT
    5 6.
    )
    x
    CROSS
    JOIN
    7.
    (
    SELECT
    1
    b
    UNION
    ALL
    SELECT
    2
    UNION
    ALL
    SELECT
    3 8.
    UNION
    ALL
    SELECT
    4
    UNION
    ALL
    SELECT
    5 9.
    )
    y
    CROSS
    JOIN
    10.
    (
    SELECT
    1
    c
    UNION
    ALL
    SELECT
    2
    UNION
    ALL
    SELECT
    3 11.
    UNION
    ALL
    SELECT
    4
    UNION
    ALL
    SELECT
    5 12.
    )
    z
    13.
    )
    x
    14.
    WHERE
    ASCII
    (
    'A'
    )
    + num
    -1
    BETWEEN
    ASCII
    (
    'A'
    )
    AND
    ASCII
    (
    'Z'
    )

    Чтобы таблица выглядела более патриотично, достаточно заменить латинские буквы a и A на неотличимые на вид русские — а и А, а z и Z на я и
    Я. Вот только буквы «ё» вы не увидите в этой таблице, так как в кодовой таблице ASCII эти символы лежат отдельно, что легко проверить:
    1.
    SELECT
    ASCII
    (
    'ё'
    )
    2.
    UNION
    ALL
    3.
    SELECT
    ASCII
    (
    'Ё'
    )
    ;
    Полагаем, что для вас не составит сложности добавить эту букву в таблицу при необходимости.
    Рассмотрим теперь задачу определения нахождения искомой подстроки в строковом выражении. Для этого могут использоваться две функции
    — CHARINDEX и PATINDEX. Обе они возвращают начальную позицию
    (позицию первого символа подстроки) подстроки в строке.
    Функции
    CHARINDEX и
    PATINDEX
    Функция CHARINDEX имеет синтаксис:
    1. CHARINDEX
    (
    искомое_выражение, строковое_выражение
    [
    , стартовая_позиция
    ])
    Здесь необязательный целочисленный параметр стартовая_позиция определяет позицию в строковом выражении, начиная с которой выполняется поиск. Если этот параметр опущен, поиск выполняется от начала строкового_выражения. Например, запрос
    1.
    SELECT
    name
    2.
    FROM
    Ships
    3.
    WHERE
    CHARINDEX
    (
    'sh'
    , name
    )
    >
    0
    ;
    будет выводить те корабли, в которых имеется сочетание символов «sh».
    Здесь используется тот факт, что если искомая строка не будет обнаружена, то функция CHARINDEX возвращает 0. Результат выполнения запроса будет содержать следующие корабли:
    name
    Kirishima
    Musashi
    Washington
    Следует отметить, что если искомая подстрока либо строковое выражение есть NULL, то результатом функции тоже будет NULL.
    Следующий пример определяет позиции первого и второго вхождения символа a в имени корабля California:
    1.
    SELECT
    CHARINDEX
    (
    'a'
    ,name
    )
    first_a,
    2. CHARINDEX
    (
    'a'
    , name, CHARINDEX
    (
    'a'
    , name
    )
    +1
    )
    second_a
    3.
    FROM
    Ships
    4.
    WHERE
    name=
    'California'
    ;
    Обратите внимание, что при определении второго символа в функции CHARINDEX используется стартовая позиция, которой является позиция следующего за первой буквой a символа — CHARINDEX('a', name)+1.
    Правильность результата — 2 и 10 — легко проверить.
    Функция PATINDEX имеет следующий синтаксис:
    1. PATINDEX
    (
    '%образец%'
    , строковое_выражение
    )
    Главное отличие этой функции от CHARINDEX заключается в том, что поисковая строка может содержать подстановочные знаки — % и _. При этом концевые знаки % являются обязательными. Например, использование этой функции в первом примере будет иметь вид

    1.
    SELECT
    name
    2.
    FROM
    Ships
    3.
    WHERE
    PATINDEX
    (
    '%sh%'
    , name
    )
    >
    0
    ;
    А вот, например, как можно найти имена кораблей, которые содержат последовательность из трех символов, первый и последний из которых есть e:
    1.
    SELECT
    name
    2.
    FROM
    Ships
    3.
    WHERE
    PATINDEX
    (
    '%e_e%'
    , name
    )
    >
    0
    ;
    Результат выполнения этого запроса выглядит следующим образом:
    Name
    Revenge
    Royal
    Sovereign
    Функция
    RIGHT
    Парная к LEFT функция RIGHT возвращает заданное число символов справа из строкового выражения:
    1.
    RIGHT
    (
    строковое выражения, число символов
    )
    Вот, например, как можно определить имена кораблей, которые начинаются и заканчиваются на одну и ту же букву:
    1.
    SELECT
    name

    2.
    FROM
    Ships
    3.
    WHERE
    LEFT
    (
    name,
    1
    )
    =
    RIGHT
    (
    name,
    1
    )
    ;
    То, что в результате мы получим пустой результирующий набор, означает, что таких кораблей в базе данных нет. Давайте возьмем комбинацию значений
    — класс и имя корабля.
    Соединение двух строковых значений в одно называется конкатенацией, и в
    SQL
    Server для этой операции используется знак «+» (в стандарте «||»). Итак,
    1.
    SELECT
    *
    2.
    FROM
    (
    SELECT
    class +
    ' '
    + name
    AS
    cn
    3.
    FROM
    Ships
    4.
    )
    x
    5.
    WHERE
    LEFT
    (
    cn,
    1
    )
    =
    RIGHT
    (
    cn,
    1
    )
    ;
    Здесь мы разделяем пробелом имя класса и имя корабля. Кроме того, чтобы не повторять всю конструкцию в качестве аргумента функции, используем подзапрос. Результат будет иметь вид:
    1   ...   22   23   24   25   26   27   28   29   ...   47

    написать администратору сайта