Георгий Устинов

Георгий Устинов

По верхушкам нахватался.

Фамилия и инициалы из полного имени в SQLite, OpenOffice Calc, Excel

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

Если вы много работаете с документами, вы обязательно столкнетесь с необходимостью быстро сократить несколько десятков или сотен русских имен.

OpenOffice Calc

Всё сказанное работает аналогично в LibreOffice Calc.

С помощью формул

Можно сделать формулу, сокращающую полное имя, хоть это и не практично, т.к. там нет функции split и нельзя многократно в формуле обращаться к ее фрагменту (нельзя определить и использовать функцию в формуле).

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

Обратите внимание, что номера символов, возвращаемые функцией SEARCH, начинаются с единицы, а не с нуля.

Конкатенация делается амперсандом. Так можно преобразовать части имени в инициал:

=LEFT(СТРОКА; 1) & "."

Наивное нахождение фамилии, имени и отчества:

=LEFT(ФИО; SEARCH(" "; ФИО)-1)

=MID(ФИО;
    SEARCH(" "; ФИО)+1;
    SEARCH(" "; ФИО; SEARCH(" "; ФИО)+1)
        - (SEARCH(" "; ФИО)+1))

=RIGHT(ФИО;
    LEN(ФИО) - SEARCH(" "; ФИО;
        SEARCH(" "; ФИО)+1))

Эти формулы перестанут работать

  1. если пробелов больше, чем нужно — удалить лишние по бокам и между словами можно функцией TRIM;
  2. если части имени разделены символами перевода строки CHAR(10) или возврата каретки CHAR(13) — можно решить автозаменой, функцией SUBSTITUTE;
  3. если отчество отсутствует, т.к. формулы опираются на позицию второго пробела.

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

=LEFT(
  TRIM(SUBSTITUTE(ФИО; CHAR(10); " "));
  SEARCH(
    " ";
    TRIM(SUBSTITUTE(ФИО; CHAR(10); " "))
  ) - 1)
& " " & MID(
  TRIM(SUBSTITUTE(ФИО; CHAR(10); " "));
  SEARCH(" "; TRIM(SUBSTITUTE(ФИО; CHAR(10); " ")))+1;
  1) & "."
& IF(
  ISNUMBER(
    SEARCH(
      " ";
      TRIM(SUBSTITUTE(ФИО; CHAR(10); " "));
      SEARCH(
        " ";
        TRIM(SUBSTITUTE(ФИО; CHAR(10); " "))
      )+1));
  " " & LEFT(RIGHT(
    TRIM(SUBSTITUTE(ФИО; CHAR(10); " "));
    LEN(TRIM(SUBSTITUTE(ФИО; CHAR(10); " ")))
    - SEARCH(
      " "; TRIM(SUBSTITUTE(ФИО; CHAR(10); " "));
      SEARCH(
        " ";
        TRIM(SUBSTITUTE(ФИО; CHAR(10); " "))
      )+1)); 1) & ".";
  "")

Живой пример: fio.ods.

С помощью макросов

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

Я практически не занимался программированием макросов для офисных пакетов. Сделал набрсок библиотеки VBA, которую можно подгрузить в файл OpenOffice Calc.

Скачать Fio.zip

Для этого распакуйте архив, затем откройте свой документ, выберите в меню Сервис → Макросы → Управление макросами → Basic → Управление → Библиотеки → Приложение/документ → выбираем свой документ → Добавить (Импорт) → заходим в папку Fio и выбираем script.xlb.

Когда библиотека подгружена, можно будет делать так.

Формула Значение
=FIOLASTNAME(ФИО) Фамилия
=FIOFIRSTNAME(ФИО) Имя
=FIOPATRONYMIC(ФИО) Отчество
=FIOSHORTNAME(ФИО; 1) Фамилия И. О.
=FIOSHORTNAME(ФИО; 0) Фамилия И.О.

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

С помощью аддона (расширения)

Я пока еще исследую эту возможность. Похоже, расширения лучше писать на чем-то вроде Python.

Excel

Понимает функции только с названиями на языке локализации. Легко найти полные таблицы соответствия названий. У нижеприведенных функций, поведение и порядок аргументов одинаковы в Excel и OO Calc — достаточно воспользоваться автозаменой (при сохранении файла OO Calc в формате Excel, формулы преобразовываются автоматически).

OO Calc, английский Excel русский Excel
TRIM СЖПРОБЕЛЫ
SUBSTITUTE ПОДСТАВИТЬ
CHAR СИМВОЛ
LEN ДЛСТР
SEARCH ПОИСК
LEFT ЛЕВСИМВ
MID ПСТР
RIGHT ПРАВСИМВ
ISNUMBER ЕЧИСЛО
IF ЕСЛИ

Получается такая формула:

=ЛЕВСИМВ(
  СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ФИО; СИМВОЛ(10); " "));
  ПОИСК(
    " ";
    СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ФИО; СИМВОЛ(10); " "))
  ) - 1)
& " " & ПСТР(
  СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ФИО; СИМВОЛ(10); " "));
  ПОИСК(" "; СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ФИО; СИМВОЛ(10); " ")))+1;
  1) & "."
& ЕСЛИ(
  ЕЧИСЛО(
    ПОИСК(
      " ";
      СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ФИО; СИМВОЛ(10); " "));
      ПОИСК(
        " ";
        СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ФИО; СИМВОЛ(10); " "))
      )+1));
  " " & ЛЕВСИМВ(ПРАВСИМВ(
    СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ФИО; СИМВОЛ(10); " "));
    ДЛСТР(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ФИО; СИМВОЛ(10); " ")))
    - ПОИСК(
      " "; СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ФИО; СИМВОЛ(10); " "));
      ПОИСК(
        " ";
        СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ФИО; СИМВОЛ(10); " "))
      )+1)); 1) & ".";
  "")

Живой пример: fio.xls.

SQLite

Стандартными функциями

Удивительно, что в SQLite тоже отсутствует функция split. Она не имеет смысла, пока в этой СУБД нет массивов.

Набор функций для обработки строк сильно отличается от набора, определенного в стандарте SQL-92.

Функции substring и trim в стандарте определяются так:

SUBSTRING(символы FROM позиция [FOR длина])
TRIM([LEADING|TRAILING|BOTH] символ FROM строка)

В SQLite эти же по смыслу функции работают только с запятыми:

SUBSTRING(символы, позиция[, длина])
TRIM(строка[, символы])

Причем trim всегда применяется с обеих сторон (в остальных случаях предлагается использовать ltrim и rtrim).

У substring есть сокращенная форма — substr.

Функции upper и lower в базовой поставке SQLite преобразовывают только латинские буквы. Там нет функций convert и translate (вместо translate можно использовать вложенные друг в друга вызовы replace).

В стандарте есть выражение POSITION. В SQLite вместо него функция instr.

POSITION(подстрока in строка)
instr(строка, подстрока)

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

Функция trim в SQL работает как в большинстве языков программирования, т.е. не убирает лишние пробелы между словами. В базовой поставке SQLite также нет автозамены по регулярным выражениям, поэтому, не используя расширения, сделать формулу, аналогичную нашей экселевской, не удастся.

Можно «малой кровью» получить близкий эквивалент, но он перестанет работать, если между словами слишком много пробелов.

WITH trimmed (id, name) AS (
  SELECT
  id,
  trim(
  replace(replace(replace(replace(
  replace(replace(
    full_name,
    char(10), ' '), char(13), ' '),
  '  ', ' '), '  ', ' '),
  '  ', ' '), '  ', ' ')
  )
  FROM persons
)
SELECT
  substr(t.name, 1, instr(t.name, ' ') - 1)
  || ' ' ||
  substr(t.name, instr(t.name, ' ') + 1, 1)
  || '.' ||
  iif(
    instr(
      substr(t.name, instr(t.name, ' ') + 1),
      ' '
    ) > 0,
    ' ' || substr(t.name,
    instr(t.name, ' ') + 1 +
    instr(
      substr(t.name, instr(t.name, ' ') + 1),
      ' '
    ), 1) || '.',
    ''
  ) AS surname_and_initials
FROM trimmed t
ORDER BY t.id

Тестовые данные: fio.sql

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

WITH trimmed (id, name) AS (
  SELECT
  id,
  trim(
    replace(replace(
    full_name,
    char(10), ' '), char(13), ' ')
  )
  FROM persons
),
splitted (id, head, tail) AS (
  SELECT
  id,
  substr(t.name, 0, instr(t.name, ' ')),
  trim(substr(t.name, instr(t.name, ' ') + 1))
  FROM trimmed t
)
SELECT
  s.head || ' ' ||
  substr(s.tail, 1, 1) || '.' ||
  iif(
    instr(s.tail, ' ') > 0,
    ' ' || substr(trim(substr(
      s.tail,
      instr(s.tail, ' ') + 1
    )), 1, 1) || '.',
    ''
  ) AS surname_and_initials
FROM splitted s
ORDER BY s.id

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

SELECT
  substr(trim(replace(replace(full_name, char(10), ' '), char(13), ' ')), 0, instr(trim(replace(replace(full_name, char(10), ' '), char(13), ' ')), ' '))
  || ' ' ||
  substr(trim(substr(trim(replace(replace(full_name, char(10), ' '), char(13), ' ')), instr(trim(replace(replace(full_name, char(10), ' '), char(13), ' ')), ' ') + 1)), 1, 1)
  || '.' ||
  iif(
    instr(
      trim(substr(trim(replace(replace(full_name, char(10), ' '), char(13), ' ')), instr(trim(replace(replace(full_name, char(10), ' '), char(13), ' ')), ' ') + 1)),
      ' '
    ) > 0,
    ' ' || substr(trim(substr(
      trim(substr(trim(replace(replace(full_name, char(10), ' '), char(13), ' ')), instr(trim(replace(replace(full_name, char(10), ' '), char(13), ' ')), ' ') + 1)),
      instr(trim(substr(trim(replace(replace(full_name, char(10), ' '), char(13), ' ')), instr(trim(replace(replace(full_name, char(10), ' '), char(13), ' ')), ' ') + 1)), ' ') + 1
    )), 1, 1) || '.',
    ''
  ) AS surname_and_initials
FROM persons
ORDER BY id

Используя sqlean

Опишу позже.

Вкратце: sqlean — это набор расширений для SQLite, который значительно упрощает многие задачи, включая работу со строками.