Удалите все специальные символы без использования хранимых процедур и функций
Это мой образец текста в данных
this is ! Alex23 & and % doing/ *SQL2019
и я хочу этот результат
this is Alex23 and doing SQL2019
В моей организации нам не разрешено использовать хранимые процедуры или функции. В настоящее время мы используем эти операторы для этого:
update tablename set columnname = replace(columnname, '!', '')
update tablename set columnname = replace(columnname, '@', '')
update tablename set columnname = replace(columnname, '#', '')
update tablename set columnname = replace(columnname, '$', '')
update tablename set columnname = replace(columnname, '%', '')
update tablename set columnname = replace(columnname, '&', '')
Есть ли способ лучше?
Используйте TRANSLATE, чтобы заменить несколько символов одновременно.
Специальные символы, которые вы упомянули в своем вопросе, — это еще не все, что существует. Так действительно ли вам нужно удалить все специальные символы или только некоторые из них (те, которые вы указали в вопросе)? И если вы заметили, что вам не нужны такие символы в этом столбце, не думаете ли вы, что вам следует создать проверочное ограничение, чтобы предотвратить их появление?
Кажется, вы хотите свернуть пространство вокруг удаленных персонажей. Каковы правила всей этой операции?
Я думаю, что-то вроде dbfiddle.uk/2m3ug7Hm должно подойти, но вопрос пока не совсем ясен.
Какая у вас версия SQL Server?
Ответы 1
Типичный способ удаления известного набора символов — использовать комбинацию TRANSLATE() и REPLACE(), чтобы сначала сопоставить все неразрешенные символы с одним неразрешенным символом, а затем исключить все вхождения этого символа.
REPLACE(TRANSLATE(string, 'aeiou', REPLICATE('a', LEN('aeiou'))), 'a', '')
Однако, если мы начинаем со списка символов, которые нужно сохранить, нам сначала нужно преобразовать его в список символов, которые нужно удалить. Я могу придумать два способа сделать это:
- Создайте весь набор символов и вычтите символы, которые нужно сохранить. Это возможно для
VARCHAR, но не дляNVARCHAR. - Удалите символы-хранители из исходной строки и используйте этот промежуточный результат в качестве символов для удаления во второй операции.
Ниже демонстрируется второй метод:
DECLARE @allowed NVARCHAR(100) =
N'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
+ N'0123456789'
+ ' '
SELECT T.*, C.cleaned
FROM tablename T
CROSS APPLY (
SELECT REPLACE(TRANSLATE(T.columnname
, @allowed, REPLICATE('A', LEN(@allowed + 'x') - 1))
, 'A', '')
AS unallowed
) U
CROSS APPLY (
SELECT REPLACE(TRANSLATE(T.columnname
, U.unallowed, REPLICATE('@', LEN(U.unallowed + 'x') - 1))
, '@', '')
AS cleaned
) C
Выше я использовал фиксированные символы «A» и «@». Если разрешенные/недопустимые строки заранее неизвестны, вместо этого вы можете использовать LEFT(@allowed, 1) и LEFT(U.unallowed, 1).
LEN(string +'x') - 1 необходим, потому что LEN() не учитывает конечные пробелы. Функция REPLICATE() используется для создания строки перевода в ту же длину, что и строка перевода из. Удобно, что функция TRANSLATE() не жалуется на дубликаты в строке перевода.
См. этот вопрос, чтобы узнать, как исключить повторяющиеся пробелы из последней строки. Далее следует адаптация верхнего ответа:
CROSS APPLY (
SELECT REPLACE(REPLACE(REPLACE(C.cleaned,
' ', ' @'), '@ ', ''), ' @', ' ')
AS dedupedspaces
) D
Вы также можете добавить TRIM() к приведенному выше, если вам нужно удалить все ведущие и конечные пробелы.
Результаты (с некоторыми дополнительными данными испытаний):
См. эту db<>скрипку.
я не знаю, почему мой sql выдает ошибку, что ПЕРЕВОД не является встроенной функцией.
Символ TRANSLATE() появился в SQL Server 2017. Если вы используете еще более старую версию, вам следует рассмотреть возможность обновления. Какую информацию о версии вы получаете при запуске команды select @@version?
Вы можете вложить несколько операторов замены в одно обновление.