Объединение столбцов разных форматов

#sql #sql-server

#sql #sql-сервер

Вопрос:

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

Я использую SQL Server. Данные выглядят следующим образом Таблица 1:

 74-123-58
896-777-92
4567-78

Таблица 2:

 00007400123
00089600777
00456700078

В таблице 1 ключ разделен на элементы отдельно тире, при этом 3-й элемент (не найден в таблице 2) иногда отсутствует.

В таблице 2 всегда первый элемент с оставленным нулем заполнен до 6 символов, а 2-й элемент с оставленным нулем заполнен до 5 символов.

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

Есть яркие идеи?

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

Ответ №1:

Они оба выглядят как строки, поэтому используйте like . Вам нужно выполнить некоторую обработку строк, но я думаю, что это работает:

 select . . .
from t1 join
     t2
     on t1.key like cast(left(t2.key, 6)   0 as varchar(255))   '-'  
                    cast(right(t2.key, 5)   0 as varchar(255))   '%';

Однако вам действительно следует исправить эту проблему в своих данных.

Примечание: Приведенное выше имеет проблему, поскольку второй ключ может быть равен 1, и он будет соответствовать 100. Это можно исправить, убедившись, что дефис следует. Но нам нужно быть осторожными с ключами, состоящими из двух частей:

 select . . .
from t1 join
     t2
     on t1.key   '-' like cast(left(t2.key, 6)   0 as varchar(255))   '-'  
                          cast(right(t2.key, 4)   0 as varchar(255))   '-%';

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

Ответ №2:

В зависимости от того, в каком направлении вы хотите двигаться, я вижу два варианта:

 on t2.[key] =
    right(
        '000000'  
        left(
            t1.[key],
            charindex('-', t1.[key]) - 1
        ),
        6
    )   
    right(
        '00000'  
        substring(t1.[key],
            charindex('-', t1.[key])   1,
            charindex('-', t1.[key]   '-', charindex('-', t1.[key])   1) -
              charindex('-', t1.[key]) - 1
        ),
        5
    )

и:

 on t1.[key]   '-' like
    cast(cast(substring(t2.[key], 1, 6) as int) as varchar(6))   '-'   
    cast(cast(substring(t2.[key], 7, 5) as int) as varchar(5))   '-'   '%'

Если у вас есть версия SQL Server, format() вы можете использовать ее вместо right('000000' X, 6) метода.

Ответ №3:

Это некоторые другие методы.

 SELECT *
FROM Table1 t1
  JOIN Table2 t2 on REPLACE(t1.value1,'-','') 
  LIKE cast(left(t2.value2, 6)  0 as varchar(255))
         cast(right(t2.value2, 5)   0 as varchar(255)) '%'

или

 SELECT *
FROM Table1 t1
  JOIN Table2 t2 on REPLACE(t1.value1,'-','') 
        LIKE REPLACE(LTRIM(REPLACE(left(t2.value2, 6), '0', ' ')),' ', '0')
               REPLACE(LTRIM(REPLACE(right(t2.value2, 5), '0', ' ')),' ', '0') '%'