Заменить текст в столбце на основе значений из таблицы поиска sql
У меня есть импортированная таблица данных из Excel (пример ImportedTable), в которой есть данные и некоторые «единицы», смешанные вместе, и я пытаюсь «отфильтровать» единицы (пример LookupTable), чтобы очистить их. LookupTable может иметь почти 20 различных значений, а ImportedTable — около 2000 записей.
Попытка выбрать текст из столбца без значений из другой таблицы. Пример:
Справочная таблица:
"Id" | "Name"
'1' | '% time operational'
'2' | 'KGal'
'3' | 'Gallons'
...
Импортированная таблица:
"Id" | "Text"
'1' | 'SomeText here % time operational'
'2' | '500 KGal'
'3' | '1.05 Gallons'
'4' | '105,000'
'5' | 'TestTextKGal'
...
Желаемый результат:
'SomeText here'
'500'
'1.05'
'105,000'
'TestText'
Я знаю, что это не работает, но хотел, чтобы что-то вроде этого работало:
SELECT LTRIM(RTRIM(REPLACE([Text], (SELECT DISTINCT [Name] FROM LookupTable), '')))
FROM ImportedTable
И эти значения поиска удаляются из «Текста» в каждой записи в ImportedTable в операторе выбора (фактически не удаляя их из таблицы).
Извините, это немного вводит в заблуждение с моей стороны. «Идентификаторы» в этих двух таблицах не будут совпадать, в одной из них около 20 записей, а в другой — 2 тыс. записей. Но у большей таблицы нет внешнего ключа или чего-то подобного, чтобы я мог перейти к LookupTable.
Обход таблицы поиска с рекурсивным cte, замена текста на каждом шаге.. dbfiddle.uk/2OsEl5q0
Может ли быть несколько совпадений LookupTable для одной строки в ImportedTable? Перекрывающиеся значения LookupTable? Перекрывающиеся совпадения в ImportedTable? Вы уже указали, что не хотите, чтобы разрывы слов были значащими, 'TestTextKGal' соответствует 'KGal', поэтому «Сканторп, Северный Линкольншир, Англия» должно соответствовать 'Thor', если не учитывается регистр.
Ответы 3
Соедините две таблицы и возьмите MIN() результата всех замен:
SELECT
i.[id], MIN(TRIM(REPLACE(i.[Name], l.[Name], ''))) AS Name
FROM ImportedTable i
CROSS JOIN LookupTable l
GROUP BY i.[id]
Смотрите живую демонстрацию.
Обратите внимание, что удобное использование MIN() здесь работает только при удалении текста с конца текста. Это работает, потому что MIN() считает текст «меньше», если символы удалены с конца, например, 'TestText' «меньше, чем» 'TestTextKGal'.
Обратите также внимание на использование TRIM(...) вместо LTRIM(RTRIM(...)).
Это сработало лучше всего для моего варианта использования. Спасибо
Это решение удалит весь текст со второй вкладки, в которой есть текст ОТ первого независимо от позиции.
Я ожидаю, что все шаблоны будут встречаться только один раз, для нескольких вы должны Вам нужно повторять первый CTE, пока не будет найдено больше совпадений
производительность можно улучшить, заменив LIKE CONCAT('%' , t1.[Name] , '%') полнотекстовым поиском.
WITH CTE aS (SELECT
t2.[id], REPLACE(t2.[Text],t1.[Name],'') resttest
FROM Table1 t1 CROSS JOIN Table2 t2
WHERE t2.[Text] LIKE CONCAT('%' , t1.[Name] , '%'))
SELECT * FROM CTE
UNION ALL
SELECT [Id],[Text] FROM Table2
WHERE [Id] NOT IN (SELECT [Id] FROM CTE )
ORDER BY Id
Еще один вариант, который будет поддерживать НЕСКОЛЬКО вхождений поисковых значений в импортированном тексте.
Здесь мы преобразуем вашу таблицу в строку JSON, а затем выполняем глобальный поиск и замену через таблицу поиска. Обратите внимание на порядок убывания len([Name])... это позволит избежать коллизий (значения внутри значений).
Пример
Declare @S nvarchar(max) = ( Select * from ImportedTable for JSON Path )
Select @S=replace(@S,[Name],'')
From (Select top 10000 *
From LookupTable
Order By len([Name]) desc
) A
Select *
From OpenJSON(@S)
with ( Id int
,Text varchar(500)
)
Полученные результаты
Id Text
1 SomeText here
2 500
3 1.05
4 105,000
5 TestText
Соедините две таблицы в столбце идентификатора, затем выполните замену, используя столбцы текста и имени.