Search code examples
sqljsonsql-serverunicodearabic

Convert Unicode/UTF-8 values into Arabic Text | SQL Server

Getting JSON from an API.

{
    "data":[
        {   
        "id":"1",
        "name_ar":"\u0647\u0627\u0646\u064a \u0639\u0628\u062f  \u0645\u0633\u0644\u0645",
        "name_en":"Hani  Moussallem"
        },
        {
        "id":"2",
        "name_ar":"\u0633\u0639\u0648\u062f \u0639\u0628\u062f\u0627\u0644\u0639\u0632\u064a\u0632  \u0628\u0627\u062d\u064a\u062f\u0631\u0647",
        "name_en":"Saud Abdulaziz Bahaidarah "
        }
    ]
}

which is actually Arabic text as below:

{
    "data": [
        {
            "id": "1",
            "name_ar": "هاني عبد  مسلم",
            "name_en": "Hani  Moussallem"
        },
        {
            "id": "2",
            "name_ar": "سعود عبدالعزيز  باحيدره",
            "name_en": "Saud Abdulaziz Bahaidarah "
        }
    ]
}

to get Arabic text writing the following SQL query but getting Unicode values instead of Arabic. How do I convert these Unicode/UTF8 values into Arabic?

here is the SQL Script:

SELECT * FROM OPENJSON(N'{
    "data":[
        {   
        "id":"1",
        "name_ar":"\u0647\u0627\u0646\u064a \u0639\u0628\u062f  \u0645\u0633\u0644\u0645",
        "name_en":"Hani  Moussallem"
        },
        {
        "id":"2",
        "name_ar":"\u0633\u0639\u0648\u062f \u0639\u0628\u062f\u0627\u0644\u0639\u0632\u064a\u0632  \u0628\u0627\u062d\u064a\u062f\u0631\u0647",
        "name_en":"Saud Abdulaziz Bahaidarah "
        }
    ]
}')

enter image description here

Solution

  • When you run OPENJSON you are only parsing the JSON document's top level. The value is returned as a string of unparsed JSON. If you parse down to the right level, you'll get the unicode values parsed correctly. EG

    SELECT * FROM OPENJSON(N'{
    "data":[
        {   
        "id":"1",
        "name_ar":"\u0647\u0627\u0646\u064a \u0639\u0628\u062f  \u0645\u0633\u0644\u0645",
        "name_en":"Hani  Moussallem"
        },
        {
        "id":"2",
        "name_ar":"\u0633\u0639\u0648\u062f \u0639\u0628\u062f\u0627\u0644\u0639\u0632\u064a\u0632  \u0628\u0627\u062d\u064a\u062f\u0631\u0647",
        "name_en":"Saud Abdulaziz Bahaidarah "
        }
    ]
}', '$.data')
with (
       id int,
       name_ar nvarchar(200),
       name_en nvarchar(200)
      )

    outputs

          id          name_ar                       name_en
----------- ----------------------------- -------------------------------
1           هاني عبد  مسلم                 Hani  Moussallem
2           سعود عبدالعزيز  باحيدره       Saud Abdulaziz Bahaidarah