Замена нескольких строковых элементов из столбца

Я пытаюсь очистить столбец текста, который у меня есть в базе данных SQL-сервера (в Azure, если это имеет значение).

Текст содержит имена людей с множеством аффиксов (доктор, профессор, доктор медицинских наук, доктор философии, MSC и множество сомнительных символов, которые мне не нужны.

Есть так много вещей, которые я хочу удалить из этих строк - часто несколько раз в одном поле, - что я подумал, что лучшим способом было бы добавить их в таблицу и использовать это для выполнения и замены каждой из них на пробел, прежде чем окончательно обрезать и заменить несколько пробелов одним, так как вложенная формула замены выглядит безумной, когда помещается даже 1/3 всех терминов, которые мне нужно удалить из столбца.

Наконец, я хочу, чтобы имя было разделено на 1-е имя, отчество и фамилию. Все красиво и чисто, никакой другой информации.

До сих пор я обрабатывал данные в SSIS и исследовал стандартные преобразования T-SQL (с использованием вложенной replace (), но которые настолько выросли, что это было непристойно). Я исследовал производные столбцы - снова кажется, что вложенная замена - единственный реальный вариант здесь !? Я наконец-то пытался найти решение, используя скриптовый компонент, но, будучи опытным любителем в этом вопросе, я не мог понять, как его использовать, и не нашел никаких примеров.

Эффективность также вызывает беспокойство, поскольку мне нужно обработать около 800 000 имен на начальном этапе.

Я был бы очень благодарен за любую помощь в этом вопросе, так как я долго искал, но не нашел ничего, что могло бы помочь напрямую.

Вы идете в кроличью нору. Уделите несколько минут, чтобы прочитать Программисты верят в ложь об именах. Это поверхностное представление о рассматриваемой задаче.

Eric Brandt 30.10.2018 12:48

Я бы рассмотрел Услуги по качеству данных или пользовательское внешнее приложение, написанное на любом удобном языке. Проблема чрезвычайно обширна.

Paweł Dyl 30.10.2018 12:48

Спасибо за комментарии: Программисты лжи об именах верят - Единственная удачная часть этой задачи состоит в том, что точность 99% (95%?) Абсолютно подходит для этого, и я сохраню исходное имя для полной идентификации на заключительном этапе нашего процесс.

Weevie 30.10.2018 12:53

Спасибо также за комментарий о службах качества данных, но в верхней части страницы говорится, что это не актуально для базы данных SQL Azure, поэтому не уверен, что могу это рассмотреть?

Weevie 30.10.2018 12:55
Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
2
4
418
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Это касается только части вопроса, однако я хотел подробнее остановиться на этом моменте разделения имен. Я не ожидаю голосований здесь, но в комментарии не будет места.

Во первых «Наконец, я хочу, чтобы имя было разделено на 1-е имя, отчество, фамилию. Все красиво и чисто, без какой-либо другой информации». не будет. Имена имеют слишком много вариаций. Прочтите Программисты верят в ложь об именах, это может показаться глупым, но это правда.

Скажем, например, вы предполагать (ошибочно), что имя разбито на несколько частей, разделенных пробелами. Первая часть - это титул, вторая - имя, фамилия - это фамилия, а все, что находится между ними, - это отчество. Вот такие примеры:

Mr John Smith
Mrs Jane Alice Wallis Smith
Mr Dick van Dyke
Sarah Brown

Очевидно, для начала, для первого человека «Джон» - это его имя, «Смит» - фамилия, а мистер - титул. А как насчет второго? Уоллис - второе имя? если фамилия может быть двойной (откуда ты знаешь?) А как насчет последнего человека? «Ван Дайк» - полная фамилия. Затем у вас есть последняя запись, в которой даже отсутствует заголовок; что там происходит?

Как я уже сказал, вы не можете разделить имя с помощью SQL. Для этого вам понадобятся умные инструменты машинного обучения очень.

Спасибо за комментарий, как я только что упомянул в ответе на комментарий - некоторые неверные данные абсолютно нормальны в этом процессе, поскольку заключительным этапом будет ручная обработка некоторых основных данных, поэтому я сохраню полное неподдельное оригинальное имя на будущее использованная литература.

Weevie 30.10.2018 12:58
Ответ принят как подходящий

Кроличья нора, запросы полных имен, хранящиеся в одном поле, а затем анализ этого в T-SQL и все заявления об отказе от ответственности.

Очистка данных, особенно столбцов text / varchar / nvarchar, является сложной задачей, независимо от элементов, упомянутых выше, поскольку обнаруженный вложенный REPLACE может выйти из-под контроля и сложен в управлении.

В t-sql можно сделать несколько вещей, которые, возможно, помогут в том, что вы пытаетесь выполнить. Все это потребует нескольких проходов данных. Я вижу, что это разбито на 2 «категории» очистки. 1 - удалить определенные слова, 2 - специальные символы.

  • Поместите «слова», в вашем случае префикс и суффикс имени, в таблицу, которую вы хотите исключить.
  • Удалите практически все не-альфа-символы.

Что, если приставка или суффикс моего имени будет «Дин» или «Мисс» или ... и имя будет «Дин Смит» или «Мисси» или ..., ага, вот она, кроличья нора. Помимо этого, вот несколько примеров кода, которые вы можете попробовать.

--Test table for words I want to strip out.
DECLARE @WordsToRemove TABLE
    (
        [word] NVARCHAR(200)
    );

--Test table for my data I want to clean
DECLARE @TestData TABLE
    (
        [Data] NVARCHAR(500)
      , [CleanData] NVARCHAR(500)
    );

--Flag I am using to keep my while loops going
DECLARE @Continue INT;


--Insert of the words I want to remove.
INSERT INTO @WordsToRemove (
                         [word]
                     )
VALUES ( 'DR' )
     , ( 'D.R.' )
     , ( 'M.D.' )
     , ( 'md' )
     , ( 'Prof.' );

--Insert test names to clean.  I have a while loop here, was loading my test table with 10000+ recrods to see performance.
--You can leave the while loop here commented out just to see how the code works.  I got under 2 minutes for 100000+ records.  Your mileage may vary.
--WHILE (SELECT COUNT(*) FROM   @TestData) < 100000
    --BEGIN
        INSERT INTO @TestData (
                              [Data]
                          )
        VALUES ( N'DR Jimmy Smith' )
             , ( 'D.R. John Jones M.D.' )
             , ( 'Timothy Neal DR md' )
             , ( 'Prof. Bob Smith Dr M.D.' )
             , ( 'Taco;,,; Johns Dr. Prof.' )
             , ( 'Prof. ''#%^Special Charaters;,,; Dr. Prof.' );
    --END;

--Just updating a another column so I can save original state
UPDATE @TestData
SET    [CleanData] = [Data]


--Join to my @WordsToRemove table using PATINDEX, continue doing that until all occurrences have been removed.
--Clean out words we dont want
SET @Continue = 1;
WHILE @Continue = 1
    BEGIN
        SET @Continue = 0;
        UPDATE     [a]
        SET        [a].[CleanData] = REPLACE([a].[CleanData], [b].[word], '')
                 , @Continue = 1
        FROM       @TestData [a]
        INNER JOIN @WordsToRemove [b]
            ON PATINDEX('%' + [b].[word] + '%', [a].[CleanData]) > 0;
    END;

--Remove all non-alpha characters, preserving spaces
--PATINDEX using "%[^a-z ]%" looks of anything not a character and not a space.
SET @Continue = 1;
WHILE @Continue = 1
    BEGIN
        SET @Continue = 0;
        UPDATE [a]
        SET    [a].[CleanData] = STUFF([a].[CleanData], PATINDEX('%[^a-z ]%', [a].[CleanData]), 1, '')
             , @Continue = 1
        FROM   @TestData [a]
        WHERE  PATINDEX('%[^a-z ]%', [a].[CleanData]) > 0;
    END;

SELECT *
FROM   @TestData;

Затем оттуда, в зависимости от того, в каком формате хранятся ваши данные, извлеките каждую часть, которая вам нужна.

Большое спасибо за ответ, Тим. Я фактически использовал T-SQL / SSIS в нескольких проходах, чтобы получить требуемые результаты. Я отметил это как правильное, поскольку метод, который я использовал для решения моей проблемы, в основном, похоже, в точности отражает логику, которую вы указали в верхней части своего ответа. (Я не тестировал ни один из предоставленных вами кодов, хотя должен добавить, на случай, если он не подходит для тех, кто может просмотреть эту ветку позже). Спасибо за уделенное время. задача решена.

Weevie 01.11.2018 11:19

Другие вопросы по теме