length - ¿Cómo puedo encontrar caracteres Unicode/no ASCII en un campo NTEXT en una tabla de SQL Server 2005?

tipos de datos sql server

Question

Tengo una mesa con un par de miles de filas. Los campos de descripción y resumen son NTEXT y, a veces, tienen caracteres no ASCII en ellos. ¿Cómo puedo ubicar todas las filas con caracteres que no sean ASCII?

Answer 1

- Esta es una manera muy, muy ineficiente de hacerlo, pero debería estar bien para - tablas pequeñas. Utiliza una tabla auxiliar de números según Itzik Ben-Gan y simplemente busca los personajes con el bit 7 establecido.

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)  

Answer 2

A veces he estado usando esta declaración de "lanzamiento" para encontrar caracteres "extraños"

select 
    *
from 
    <Table>
where 
    <Field> != cast(<Field> as varchar(1000))

Answer 3

Aqui tienes:

SELECT *
FROM Objects
WHERE 
    ObjectKey LIKE ''%[^0-9a-zA-Z !"#$%&''''()*+,/-./:;<=>?@/[/^_`{|}~/]//]%'' ESCAPE ''/'

Answer 4

Empecé con la solución de @ CC1960 pero encontré un caso de uso interesante que hizo que fallara. Parece que SQL Server igualará ciertos caracteres Unicode a sus aproximaciones que no sean Unicode. Por ejemplo, SQL Server considera el carácter Unicode "coma de ancho completo" ( http://www.fileformat.info/info/unicode/char/ff0c/index.htm ) lo mismo que una coma ASCII estándar cuando se compara en una cláusula WHERE.

Para evitar esto, haga que SQL Server compare las cadenas como binarias. Pero recuerde, los binarios nvarchar y varchar no coinciden (16 bits frente a 8 bits), por lo que debe convertir su varchar nuevamente en nvarchar antes de hacer la comparación binaria:

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)))

Answer 5

Mi respuesta anterior confundía datos UNICODE / no UNICODE. Aquí hay una solución que debería funcionar para todas las situaciones, aunque todavía me encuentro con algunas anomalías. Parece que ciertos caracteres Unicode que no son ASCII para caracteres superíndices se confunden con el carácter numérico real. Es posible que pueda jugar con colaciones para evitar eso.

Espero que ya tengas una tabla de números en tu base de datos (pueden ser muy útiles), pero solo en caso de que haya incluido el código para llenarlo parcialmente también.

También es posible que necesite jugar con el rango numérico, ya que los caracteres Unicode pueden ir más allá de 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

Answer 6

Primero construya una cadena con todos los caracteres que no le interesen (el ejemplo usa el rango 0x20 - 0x7F, o 7 bits sin los caracteres de control). Cada carácter tiene el prefijo |, para usar en la cláusula de escape más adelante.

-- 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

El siguiente fragmento busca cualquier carácter que no esté en la lista. El% coincide con 0 o más caracteres. [] Coincide con uno de los caracteres dentro de [], por ejemplo, [abc] podría coincidir con a, bo c. El ^ niega la lista, por ejemplo [^ abc] coincidiría con cualquier cosa que no sea a, b, o c.

select *
from yourtable
where yourfield like ''%[^'' + @str + '']%'' escape ''|''

El carácter de escape es necesario porque si no busca caracteres como],% o _ estropearía la expresión LIKE.

Espero que esto sea útil, y gracias al comentario de JohnFX sobre la otra respuesta.

Answer 7

Probablemente no sea la mejor solución, pero tal vez una consulta como:

SELECT *
FROM yourTable
WHERE yourTable.yourColumn LIKE ''%[^0-9a-zA-Z]%''

Reemplace la expresión "0-9a-zA-Z" con algo que capture el conjunto ASCII completo (o un subconjunto que contengan sus datos).

Answer 8

Si está buscando un carácter Unicode específico, puede usar algo como a continuación.

   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%''

Answer 9

Técnicamente, creo que un NCHAR (1) es un carácter ASCII válido IF & Only SI UNICODE (@NChar) <256 y ASCII (@NChar) = UNICODE (@NChar) aunque eso puede no ser exactamente lo que pretendía. Por lo tanto, esta sería una solución correcta:

;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))

Esto también debería ser muy rápido.