I have a table in sql server 2012, where one column is nvarchar. It contains Romanian characters. We've noticed that only some of the letters 'Ș' do not show in reports at all, so I found that it depends of the keyboard settings.
There are two different keyboard settings for Romanian - Standard and Legacy. Letter 'Ș' - inserted from Rom(Standard) keyboard have ASCII code 63, from Legacy it's 170.
Letter 'Ş' with CHAR(170) - shows in reports, but CHAR(63) - doesn't - even though it's the same letter (should be).
It would be simple if I could replace char(63) with char(170), but I cannot detect rows with character 63. The next select doesn't return rows:
select * from table1 where columnname like '%'+CHAR(63)+'%'
even though if I do select ASCII(SUBSTRING(columnname , 1, 1)) it returns me '63'.
even select charindex(char(63), columnname) - returns me 0
I also tried to do collation:
select * from table1 where columnname COLLATE Latin1_general_CI_AI like N'%s%'
it doesn't help - it returns only rows with 's' and char(170).
Please help me find these rows with wrong 'Ş'
So firstly from my comments, CHAR(63) is misleading as it represents a character that sql server is unable to display:
Unable to replace Char(63) by SQL query
The issue is possibly down to your selected collation, as if I run this sample I get the 2 rows containing the special characters:
CREATE TABLE #temp ( val NVARCHAR(50) )
INSERT INTO #temp
( val )
VALUES ( N'Șome val 1' ),
( N'some val 2' ),
( N'șome other val 3' )
SELECT *
FROM #temp
WHERE val COLLATE Latin1_General_BIN LIKE N'%ș%'
OR val COLLATE Latin1_General_BIN LIKE N'%Ș%'
DROP TABLE #temp
Output
val
=================
Șome val 1
șome other val 3
The specified collation is: Latin1_General_BIN, as found in this post: