hasta - Encuentre caracteres que no sean ASCII en columnas varchar usando SQL Server
substring sql (8)
Aquí hay una UDF que construí para detectar columnas con caracteres ascii extendidos. Es rápido y puede ampliar el conjunto de caracteres que desea verificar. El segundo parámetro le permite alternar entre verificar cualquier cosa que esté fuera del conjunto de caracteres estándar o permitir un conjunto ampliado:
create function [dbo].[udf_ContainsNonASCIIChars]
(
@string nvarchar(4000),
@checkExtendedCharset bit
)
returns bit
as
begin
declare @pos int = 0;
declare @char varchar(1);
declare @return bit = 0;
while @pos < len(@string)
begin
select @char = substring(@string, @pos, 1)
if ascii(@char) < 32 or ascii(@char) > 126
begin
if @checkExtendedCharset = 1
begin
if ascii(@char) not in (9,124,130,138,142,146,150,154,158,160,170,176,180,181,183,184,185,186,192,193,194,195,196,197,199,200,201,202,203,204,205,206,207,209,210,211,212,213,214,216,217,218,219,220,221,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,248,249,250,251,252,253,254,255)
begin
select @return = 1;
select @pos = (len(@string) + 1)
end
else
begin
select @pos = @pos + 1
end
end
else
begin
select @return = 1;
select @pos = (len(@string) + 1)
end
end
else
begin
select @pos = @pos + 1
end
end
return @return;
end
USO:
select Address1
from PropertyFile_English
where udf_ContainsNonASCIIChars(Address1, 1) = 1
¿Cómo pueden devolverse las filas con caracteres que no sean ASCII utilizando SQL Server?
Si puede mostrar cómo hacerlo para una columna sería genial.
Estoy haciendo algo como esto ahora, pero no está funcionando
select *
from Staging.APARMRE1 as ar
where ar.Line like ''%[^!-~ ]%''
Para crédito adicional, si puede abarcar todas las columnas varchar en una tabla, ¡eso sería excepcional! En esta solución, sería bueno devolver tres columnas:
- El campo de identidad para ese registro. (Esto permitirá que todo el registro sea revisado con otra consulta).
- El nombre de la columna
- El texto con el carácter inválido
Id | FieldName | InvalidText |
----+-----------+-------------------+
25 | LastName | Solís |
56 | FirstName | François |
100 | Address1 | 123 Ümlaut street |
Los caracteres inválidos serían cualquiera fuera del rango de SPACE (32 10 ) a ~ (127 10 )
Aquí hay una solución para la búsqueda de una sola columna usando PATINDEX.
También muestra los caracteres StartPosition, InvalidCharacter y ASCII.
select line,
patindex(''%[^ !-~]%'' COLLATE Latin1_General_BIN,Line) as [Position],
substring(line,patindex(''%[^ !-~]%'' COLLATE Latin1_General_BIN,Line),1) as [InvalidCharacter],
ascii(substring(line,patindex(''%[^ !-~]%'' COLLATE Latin1_General_BIN,Line),1)) as [ASCIICode]
from staging.APARMRE1
where patindex(''%[^ !-~]%'' COLLATE Latin1_General_BIN,Line) >0
Este script busca caracteres que no sean ascii en una columna. Genera una cadena de todos los caracteres válidos, aquí codifica el punto 32 a 127. Luego busca las filas que no coinciden con la lista:
declare @str varchar(128)
declare @i int
set @str = ''''
set @i = 32
while @i <= 127
begin
set @str = @str + ''|'' + char(@i)
set @i = @i + 1
end
select col1
from YourTable
where col1 like ''%[^'' + @str + '']%'' escape ''|''
Hay una función definida por el usuario disponible en la web ''Parse alfanumérico''. Google UDF analizar alfanumérico y debe encontrar el código para ello. Esta función definida por el usuario elimina todos los caracteres que no se ajustan entre 0-9, az y AZ.
select dodgyColumn from myTable fcc
WHERE patindex(''%[^ !-~]%'' COLLATE Latin1_General_BIN,dodgyColumn ) >0
Eso debería traer de vuelta cualquier registro que tenga un last_name con caracteres inválidos para usted ... aunque su pregunta de puntos de bonificación es un desafío un poco más, pero creo que una declaración de caso podría manejarlo. Este es un código poco psíquico, no estoy del todo seguro de si funcionaría.
select dodgyColumn from myTable fcc
INNER JOIN dbo.Numbers32k dn ON dn.number<(len(fcc.dodgyColumn ))
WHERE ASCII(SUBSTRING(fcc.dodgyColumn , dn.Number, 1))<32
OR ASCII(SUBSTRING(fcc.dodgyColumn , dn.Number, 1))>127
Lo escribí en el buzón del foro ... así que no estoy seguro si funcionará como está, pero debería estar cerca. No estoy seguro de cómo se comportará si un solo registro tiene dos campos con caracteres no válidos tampoco.
Como alternativa, debería ser capaz de cambiar la cláusula from de una sola tabla a una subconsulta que se parece a algo así como:
select dodgyColumn
from myTable
where dbo.udf_test_ContainsNonASCIIChars(dodgyColumn , 1) = 1
El beneficio aquí es que para cada columna solo tendrá que extender la declaración del sindicato aquí, mientras que necesita poner esa comparación tres veces para cada columna en la versión del estado de cuenta de este script
He estado ejecutando este código poco con éxito
declare @UnicodeData table (
data nvarchar(500)
)
insert into
@UnicodeData
values
(N''Horse�'')
,(N''Dog'')
,(N''Cat'')
select
data
from
@UnicodeData
where
data collate LATIN1_GENERAL_BIN != cast(data as varchar(max))
Lo cual funciona bien para columnas conocidas.
Para obtener más crédito, escribí este guión rápido para buscar todas las columnas nvarchar en una tabla determinada para caracteres Unicode.
declare
@sql varchar(max) = ''''
,@table sysname = ''mytable'' -- enter your table here
;with ColumnData as (
select
RowId = row_number() over (order by c.COLUMN_NAME)
,c.COLUMN_NAME
,ColumnName = ''['' + c.COLUMN_NAME + '']''
,TableName = ''['' + c.TABLE_SCHEMA + ''].['' + c.TABLE_NAME + '']''
from
INFORMATION_SCHEMA.COLUMNS c
where
c.DATA_TYPE = ''nvarchar''
and c.TABLE_NAME = @table
)
select
@sql = @sql + ''select FieldName = '''''' + c.ColumnName + '''''', InvalidCharacter = ['' + c.COLUMN_NAME + ''] from '' + c.TableName + '' where '' + c.ColumnName + '' collate LATIN1_GENERAL_BIN != cast('' + c.ColumnName + '' as varchar(max)) '' + case when c.RowId <> (select max(RowId) from ColumnData) then '' union all '' else '''' end + char(13)
from
ColumnData c
-- check
-- print @sql
exec (@sql)
No soy partidario de SQL dinámico, pero tiene sus aplicaciones para consultas exploratorias como esta.
Para encontrar qué campo tiene caracteres inválidos:
SELECT * FROM Staging.APARMRE1 FOR XML AUTO, TYPE
Puedes probarlo con esta consulta:
SELECT top 1 ''char 31: ''+char(31)+'' (hex 0x1F)'' field
from sysobjects
FOR XML AUTO, TYPE
El resultado será:
Msg 6841, nivel 16, estado 1, línea 3 FOR XML no pudo serializar los datos para el nodo ''campo'' porque contiene un carácter (0x001F) que no está permitido en XML. Para recuperar esta información usando FOR XML, conviértala a tipo de datos binario, varbinary o de imagen y use la directiva BINARY BASE64.
Es muy útil cuando escribe archivos xml y obtiene el error de caracteres no válidos cuando lo valida.
ejecutando varias soluciones en algunos datos del mundo real: 12M filas varchar longitud ~ 30, alrededor de 9k filas dudosas, sin índice de texto completo en juego, la solución patIndex es la más rápida, y también selecciona la mayoría de las filas.
(recorrió previamente el km para establecer el caché en un estado conocido, ejecutó los 3 procesos, y finalmente ejecutó km nuevamente - las últimas 2 carreras de km dieron tiempos en 2 segundos)
solución patindex de Gerhard Weiss - Runtime 0:38, devuelve 9144 filas
Select * from Staging.APARMRE1 ar
where udf_parsealpha(ar.last_name) <> ar.last_name
la solución de números de subcadenas de MT. - Tiempo de ejecución 1:16, devuelto 8996 filas
Select id, case when udf_parsealpha(ar.last_name) <> ar.last_name then ''last name''
when udf_parsealpha(ar.first_name) <> ar.first_name then ''first name''
when udf_parsealpha(ar.Address1) <> ar.last_name then ''Address1''
end,
case when udf_parsealpha(ar.last_name) <> ar.last_name then ar.last_name
when udf_parsealpha(ar.first_name) <> ar.first_name then ar.first_name
when udf_parsealpha(ar.Address1) <> ar.last_name then ar.Address1
end
from Staging.APARMRE1 ar
where udf_parsealpha(ar.last_name) <> ar.last_name or
udf_parsealpha(ar.first_name) <> ar.first_name or
udf_parsealpha(ar.Address1) <> ar.last_name
solución udf por Deon Robertson - Runtime 3:47, devuelve 7316 filas
select id,fieldname,value from (
Select id,''last_name'' as ''fieldname'', last_name as ''value''
from Staging.APARMRE1 ar
Union
Select id,''first_name'' as ''fieldname'', first_name as ''value''
from Staging.APARMRE1 ar
---(and repeat unions for each field)
)
where udf_parsealpha(value) <> value
intente algo como esto:
DECLARE @YourTable table (PK int, col1 varchar(20), col2 varchar(20), col3 varchar(20))
INSERT @YourTable VALUES (1, ''ok'',''ok'',''ok'')
INSERT @YourTable VALUES (2, ''BA''+char(182)+''D'',''ok'',''ok'')
INSERT @YourTable VALUES (3, ''ok'',char(182)+''BAD'',''ok'')
INSERT @YourTable VALUES (4, ''ok'',''ok'',''B''+char(182)+''AD'')
INSERT @YourTable VALUES (5, char(182)+''BAD'',''ok'',char(182)+''BAD'')
INSERT @YourTable VALUES (6, ''BAD''+char(182),''B''+char(182)+''AD'',''BAD''+char(182)+char(182)+char(182))
--if you have a Numbers table use that, other wise make one using a CTE
;WITH AllNumbers AS
( SELECT 1 AS Number
UNION ALL
SELECT Number+1
FROM AllNumbers
WHERE Number<1000
)
SELECT
pk, ''Col1'' BadValueColumn, CONVERT(varchar(20),col1) AS BadValue --make the XYZ in convert(varchar(XYZ), ...) the largest value of col1, col2, col3
FROM @YourTable y
INNER JOIN AllNumbers n ON n.Number <= LEN(y.col1)
WHERE ASCII(SUBSTRING(y.col1, n.Number, 1))<32 OR ASCII(SUBSTRING(y.col1, n.Number, 1))>127
UNION
SELECT
pk, ''Col2'' BadValueColumn, CONVERT(varchar(20),col2) AS BadValue --make the XYZ in convert(varchar(XYZ), ...) the largest value of col1, col2, col3
FROM @YourTable y
INNER JOIN AllNumbers n ON n.Number <= LEN(y.col2)
WHERE ASCII(SUBSTRING(y.col2, n.Number, 1))<32 OR ASCII(SUBSTRING(y.col2, n.Number, 1))>127
UNION
SELECT
pk, ''Col3'' BadValueColumn, CONVERT(varchar(20),col3) AS BadValue --make the XYZ in convert(varchar(XYZ), ...) the largest value of col1, col2, col3
FROM @YourTable y
INNER JOIN AllNumbers n ON n.Number <= LEN(y.col3)
WHERE ASCII(SUBSTRING(y.col3, n.Number, 1))<32 OR ASCII(SUBSTRING(y.col3, n.Number, 1))>127
order by 1
OPTION (MAXRECURSION 1000)
SALIDA:
pk BadValueColumn BadValue
----------- -------------- --------------------
2 Col1 BA¶D
3 Col2 ¶BAD
4 Col3 B¶AD
5 Col1 ¶BAD
5 Col3 ¶BAD
6 Col1 BAD¶
6 Col2 B¶AD
6 Col3 BAD¶¶¶
(8 row(s) affected)