How to store UniCode strings in SQL Server 2019

Question

Dear colleagues,

When I try the following script from Managament Studio, the result is not what I would like.

SELECT 'أفغانستان'
UNION ALL SELECT 'Афганистан'
UNION ALL SELECT 'Αφγανιστάν'
UNION ALL SELECT 'Աֆղանստան'
UNION ALL SELECT 'Afghanistan'
UNION ALL SELECT 'アフガニスタン' 
UNION ALL SELECT '아프가니스탄' 
UNION ALL SELECT 'Afeganistão'
UNION ALL SELECT 'Афганистан'
UNION ALL SELECT 'อัฟกานิสถาน'
UNION ALL SELECT 'Афганістан'
UNION ALL SELECT '阿富汗';

The result:

Obviously, I have to pick the right collation. But how do I find out which collation to use for which string?

Answer 1

Actually, since you are on SQL 2019, you don't need the N. You can take any collation that ends in UTF8.

CREATE DATABASE UTF8Demo COLLATE Pashto_100_CI_AS_SC_UTF8
go
USE UTF8Demo
go
SELECT 'أفغانستان'
UNION ALL SELECT 'Афганистан'
UNION ALL SELECT 'Αφγανιστάν'
UNION ALL SELECT 'Աֆղանստան'
UNION ALL SELECT 'Afghanistan'
UNION ALL SELECT 'アフガニスタン' 
UNION ALL SELECT '아프가니스탄' 
UNION ALL SELECT 'Afeganistão'
UNION ALL SELECT 'Афганистан'
UNION ALL SELECT 'อัฟกานิสถาน'
UNION ALL SELECT 'Афганістан'
UNION ALL SELECT '阿富汗';

Answer 2

I'm sorry, beginners mistake. It is so obvious. The answer (do note the N' denoting unicode strings):

SELECT N'أفغانستان'
UNION ALL SELECT N'Афганистан'
UNION ALL SELECT N'Αφγανιστάν'
UNION ALL SELECT N'Աֆղանստան'
UNION ALL SELECT N'Afghanistan'
UNION ALL SELECT N'アフガニスタン' 
UNION ALL SELECT N'아프가니스탄' 
UNION ALL SELECT N'Afeganistão'
UNION ALL SELECT N'Афганистан'
UNION ALL SELECT N'อัฟกานิสถาน'
UNION ALL SELECT N'Афганістан'
UNION ALL SELECT N'阿富汗'

Answer 3

Thank you, Erland. As ever, I highly value your input.