How to fix accent query data sql server [closed]

1

Good morning!

Well, how do I perform the correction of data accents from a select in the sql server. For some records that were meant to come as Sergio, Sergio is coming.

    
asked by anonymous 13.07.2017 / 14:20

2 answers

1

Renan, if you need to get this data correctly at runtime, I believe the solution is below.

  

The function has been removed from this link

CREATE FUNCTION dbo.UTF8_TO_NVARCHAR(@in varchar(MAX))
   RETURNS NVarChar(MAX)
AS
BEGIN
   DECLARE @out NVarChar(MAX), @i int, @c int, @c2 int, @c3 int, @nc int

   SELECT @i = 1, @out = ''

   WHILE (@i <= Len(@in))
   BEGIN
      SET @c = Ascii(SubString(@in, @i, 1))

      IF (@c < 128)
      BEGIN
         SET @nc = @c
         SET @i = @i + 1
      END
      ELSE IF (@c > 191 AND @c < 224)
      BEGIN
         SET @c2 = Ascii(SubString(@in, @i + 1, 1))

         SET @nc = (((@c & 31) * 64 /* << 6 */) | (@c2 & 63))
         SET @i = @i + 2
      END
      ELSE
      BEGIN
         SET @c2 = Ascii(SubString(@in, @i + 1, 1))
         SET @c3 = Ascii(SubString(@in, @i + 2, 1))

         SET @nc = (((@c & 15) * 4096 /* << 12 */) | ((@c2 & 63) * 64 /* << 6 */) | (@c3 & 63))
         SET @i = @i + 3
      END

      SET @out = @out + NChar(@nc)
   END
   RETURN @out
END

The following queries check whether it worked or not.

-- Essa te retorna todos os registros da tabela

SELECT * ,
       foo = dbo.UTF8_TO_NVARCHAR ( sua_coluna_com_problemas )
  FROM sua_tabela

-- Essa te retorna apenas os registros que possuem algum problema de ENCODE

SELECT * ,
       foo = dbo.UTF8_TO_NVARCHAR ( sua_coluna_com_problemas )
  FROM sua_tabela
WHERE sua_coluna_com_problemas <> dbo.UTF8_TO_NVARCHAR(sua_coluna_com_problemas)
    
13.07.2017 / 16:02
0

This is because of the difference between the character encoding used in the column and the encoding used in the application. You should check what the column COLLATE is and then configure the same encoding ( pagecode) in the application. It is also possible to use COLLATE in the query that retrieves rows to match the pagecode used in the application.

In SQL Server it is possible to define COLLATE on 3 levels: (1) in the instance; (2) in the database and (3) in the column.

Suggested reading:

Similar topics:

13.07.2017 / 15:49