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