У меня есть таблица с несколькими тысячами строк. Поля описания и сводки - это NTEXT и иногда содержат в них символы, отличные от ASCII. Как я могу найти все строки с символами не ASCII?
Как найти символы Unicode/не-ASCII в поле NTEXT в таблице SQL Server 2005?
Ответ 1
Я иногда использовал это выражение "cast" для поиска "странных" символов
select
*
from
<Table>
where
<Field> != cast(<Field> as varchar(1000))
Ответ 2
Сначала создайте строку со всеми символами, которые вам не интересны (в примере используется диапазон 0x20 - 0x7F или 7 бит без управляющих символов.) Каждый символ имеет префикс |, для использования в предложении escape позже.
-- Start with tab, line feed, carriage return
declare @str varchar(1024)
set @str = '|' + char(9) + '|' + char(10) + '|' + char(13)
-- Add all normal ASCII characters (32 -> 127)
declare @i int
set @i = 32
while @i <= 127
begin
-- Uses | to escape, could be any character
set @str = @str + '|' + char(@i)
set @i = @i + 1
end
Следующий фрагмент ищет любой символ, отсутствующий в списке. % Соответствует 0 или более символам. [] Соответствует одному из символов внутри [], например [abc] будет соответствовать либо a, b, либо c. Элемент ^ отменяет список, например [^ abc] будет соответствовать любому, что не a, b или c.
select *
from yourtable
where yourfield like '%[^' + @str + ']%' escape '|'
Эквивалентный символ требуется, поскольку в противном случае поиск таких символов, как],% или _, испортил бы выражение LIKE.
Надеюсь, это полезно, и благодаря комментарию JohnFX на другой ответ.
Ответ 3
Технически я считаю, что NCHAR (1) является допустимым символом ASCII IF и только IF UNICODE (@NChar) < 256 и ASCII (@NChar) = UNICODE (@NChar), хотя это может быть не совсем то, что вы намеревались. Поэтому это было бы правильным решением:
;With cteNumbers as
(
Select ROW_NUMBER() Over(Order By c1.object_id) as N
From sys.system_columns c1, sys.system_columns c2
)
Select Distinct RowID
From YourTable t
Join cteNumbers n ON n <= Len(CAST(TXT As NVarchar(MAX)))
Where UNICODE(Substring(TXT, n.N, 1)) > 255
OR UNICODE(Substring(TXT, n.N, 1)) <> ASCII(Substring(TXT, n.N, 1))
Это также должно быть очень быстро.
Ответ 4
Это, вероятно, не лучшее решение, но, возможно, такой запрос, как:
SELECT *
FROM yourTable
WHERE yourTable.yourColumn LIKE '%[^0-9a-zA-Z]%'
Замените выражение "0-9a-zA-Z" тем, что фиксирует полный набор ASCII (или подмножество, содержащее ваши данные).
Ответ 5
Здесь ya go:
SELECT *
FROM Objects
WHERE
ObjectKey LIKE '%[^0-9a-zA-Z !"#$%&''()*+,\-./:;<=>[email protected]\[\^_`{|}~\]\\]%' ESCAPE '\'
Ответ 6
Я начал с решения @CC1960, но нашел интересный прецедент, который вызвал его. Кажется, что SQL Server будет сопоставлять некоторые символы Unicode с их приближениями, отличными от Unicode. Например, SQL Server рассматривает символ Unicode "полная запятая" (http://www.fileformat.info/info/unicode/char/ff0c/index.htm), как и стандартная запятая ASCII при сравнении в предложении WHERE.
Чтобы обойти это, SQL Server сравнивает строки как двоичные. Но помните, что двоичные файлы nvarchar и varchar не совпадают (16-разрядные и 8-разрядные), поэтому вам нужно снова преобразовать ваш varchar в nvarchar перед выполнением двоичного сравнения:
select *
from my_table
where CONVERT(binary(5000),my_table.my_column) != CONVERT(binary(5000),CONVERT(nvarchar(1000),CONVERT(varchar(1000),my_table.my_column)))
Ответ 7
Если вы ищете конкретный символ Юникода, вы можете использовать что-то вроде ниже.
select Fieldname from
(
select Fieldname,
REPLACE(Fieldname COLLATE Latin1_General_BIN,
NCHAR(65533) COLLATE Latin1_General_BIN,
'CustomText123') replacedcol
from table
) results where results.replacedcol like '%CustomText123%'
Ответ 8
Мой предыдущий ответ путал данные UNICODE/non-UNICODE. Вот решение, которое должно работать для всех ситуаций, хотя я все еще сталкиваюсь с некоторыми аномалиями. Кажется, что некоторые символы Unicode, отличные от ASCII, для символов надстроек смешиваются с фактическим символом числа. Возможно, вы сможете поиграть с коллаборациями, чтобы обойти это.
Надеюсь, у вас уже есть таблица чисел в вашей базе данных (они могут быть очень полезными), но на всякий случай я включил код, чтобы частично заполнить это.
Вам также может понадобиться играть с числовым диапазоном, поскольку символы Unicode могут превышать 255.
CREATE TABLE dbo.Numbers
(
number INT NOT NULL,
CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (number)
)
GO
DECLARE @i INT
SET @i = 0
WHILE @i < 1000
BEGIN
INSERT INTO dbo.Numbers (number) VALUES (@i)
SET @i = @i + 1
END
GO
SELECT *,
T.ID, N.number, N'%' + NCHAR(N.number) + N'%'
FROM
dbo.Numbers N
INNER JOIN dbo.My_Table T ON
T.description LIKE N'%' + NCHAR(N.number) + N'%' OR
T.summary LIKE N'%' + NCHAR(N.number) + N'%'
and t.id = 1
WHERE
N.number BETWEEN 127 AND 255
ORDER BY
T.id, N.number
GO
Ответ 9
- Это очень, очень неэффективный способ сделать это, но должен быть в порядке для - небольшие столы. Он использует вспомогательную таблицу чисел в соответствии с Itzik Ben-Gan и просто - ищет символы с установленным битом 7.
SELECT *
FROM yourTable as t
WHERE EXISTS ( SELECT *
FROM msdb..Nums as NaturalNumbers
WHERE NaturalNumbers.n < LEN(t.string_column)
AND ASCII(SUBSTRING(t.string_column, NaturalNumbers.n, 1)) > 127)