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