How to fix accentuation query sql server data

Asked

Viewed 3,352 times

2

Good morning!

Guys, how do I correct the accents of the data coming from a select in sql server. For some records that were to come as Sergio, is coming SÃ rgio.

sql sql-server query

  • This is because they were inserted like this. You must fix the insertion, not the output.

  • But how can I do this in the query? It has some function?

  • 1

    Gives an improved in your question, explain how the data are being entered and how they are being recovered, ie, is an application that does this or is direct in the database ?

  • @Renanbessa do not know if there is a function in SQL. What you notice is an encoding problem, so there is the possibility that an application does not have this problem i.e.: in the application the accent will appear correct if the application uses the same encoding as the one that generated the Insert.

  • @Renanbessa: what is the language of the application?

2 answers

2


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

Function has been removed of 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 queries below 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)

  • That’s right, buddy, it worked

  • 1

    saved my life, vlw

0

This is due to the difference between the character encoding used in the column and the encoding used in the application. One should check which is the COLLATE of the column and then configure in the application the same encoding (pagecode). It is also possible to use COLLATE in the query that recovers the lines, in order to reconcile with the pagecode used in the application.

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

Reading suggestion:

Similar topics:

Browser other questions tagged sql sql-server query

You are not signed in. Login or sign up in order to post.