How to retrieve wrong charset encoded strings?

user_10656503 0
Viewed 746

In my PHP script, I made a connection with the MS SQL database server with the following code,

$connectionInfo = array( "Database"=>$database,"UID"=>$uid, "PWD"=>$pwd);
$conn = sqlsrv_connect( $serverName, $connectionInfo);

Mistakenly I did forget to specify the "CharacterSet"=>"UTF-8" in the $connectionInfo. Due to this reason, some Spanish and other characters have been encoded wrong. For example, "álgebra" is stored as "álgebra". Now that I have set the proper character set during the connection to the database, new data is stored correctly. But how can I restore the original strings that had been encoded wrongly and stored already?

special-characters character-encoding sql-server php
2 Answers

Most likely you haven't lost anything. Just convert the string/column to binary and then convert from binary to string with the proper encoding.

--2019
select cast(0xC3A16C6765627261 as varchar(100));

declare @t table(thechar varchar(100) collate Latin1_General_100_CI_AI_SC_UTF8)
insert into @t (thechar) values (0xC3A16C6765627261);

select *
from @t;
user_124036120

Sometimes I use this tiny function to convert from UTF8 that may help you:

create function FromUtf8(@src varchar(8000)) returns varchar(8000) as
begin
    declare @c char, @i int
    select @i = patIndex('%[ÂÃ][€-¿]%', @src collate Latin1_General_BIN)
    while @i > 0
        select  @c = char(((ascii(substring(@src, @i, 1)) & 31) * 64)
                         + (ascii(substring(@src, @i + 1, 1)) & 63)),
                @src = stuff(@src, @i, 2, @c),
                @i = patIndex('%[ÂÃ][€-¿]%', @src collate Latin1_General_BIN)
    return @src
end
user_106999320
Related