SQL Server 2017 Collation for special character (Hawaiian 'Okina) leads to "?" string behavior

Question

I need to insert some Hawaiian 'Okina string name as 'Kapiʻolani'; However in the SQL Server 2017 database it shows like Kapi?olani with a question mark.

I did some research and tried like this article's solution:

CREATE TABLE Organization3 (Name NVARCHAR(250) COLLATE Japanese_XJIS_140_BIN2); 

After insert value:

INSERT INTO Organization3 
      ([Name])
      values
      ('Kapi''olani')
update unicas_config.dbo.Organization3  set Name = 'Kapiʻolani'

It still shows like this: Kapi?olani (with question mark).

Is any solution to make it show correctly? Thank you so much

Answer 1

You should use the N prefix (i.e. N'Kapiʻolani') to specify that it's an Unicode string (as already pointed out in the comments).

The collation matters only for sorting and comparisons (as well as string-related functions that do comparisons to perform their task).

For example, the following SELECT-s return different results:

CREATE TABLE #t (x NVARCHAR(100))
INSERT INTO #t VALUES (N'Kapiʻolani'),(N'KAPIʻOLANI'),(N'Kapi''olani'),(N'Kapiolani'),(N'Kapi olani')

SELECT DENSE_RANK() OVER (ORDER BY x COLLATE Latin1_General_100_CI_AI), * FROM #t ORDER BY 1
SELECT DENSE_RANK() OVER (ORDER BY x COLLATE Latin1_General_100_CI_AS), * FROM #t ORDER BY 1
SELECT DENSE_RANK() OVER (ORDER BY x COLLATE Latin1_General_100_CS_AI), * FROM #t ORDER BY 1
SELECT DENSE_RANK() OVER (ORDER BY x COLLATE Latin1_General_100_CS_AS), * FROM #t ORDER BY 1
SELECT DENSE_RANK() OVER (ORDER BY x COLLATE Latin1_General_100_BIN), * FROM #t ORDER BY 1

DROP TABLE #t

Use the collation that returns the data that you are expecting.