Cannot find letter "ş" or "Ş" inserted from Romanian (standard) keyboard
I have a table in sql server 2012 where one column is nvarchar. It contains Romanian characters. We noticed that only some of the "" letters do not appear in the reports at all, so I found that it depends on the keyboard settings.
There are two different keyboard settings for Romanian - Standard and Legacy. The letter "Ş" inserted from the keyboard Rom (Standard) has ASCII code 63, from Legacy it 170.
The letter 'Ş' with CHAR (170) is displayed in reports, but CHAR (63) is not - even if it is the same letter (should be).
It would be simple if I could replace CHAR (63) with CHAR (170), but I cannot find lines with character 63. The following selection returns no lines:
select * from table1 where columnname like '%'+CHAR(63)+'%'
although if I do select ASCII(SUBSTRING(columnname , 1, 1))
it will return "63" to me.
even select charindex(char(63), columnname)
- returns me 0
I also tried sorting:
select * from table1 where columnname COLLATE Latin1_general_CI_AI like N'%s%'
it doesn't help - it only returns strings with 's' and CHAR (170).
Please help me find these lines with the wrong 'Ş'
source to share
So firstly, from my comments, it CHAR(63)
is misleading as it represents a character that sql server cannot display:
Cannot replace Char (63) with SQL query
Perhaps the problem is related to the collation you selected, as if I were running this sample I get two strings containing 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 collation specified is Latin1_General_BIN as stated in this post:
source to share
WHERE columnname LIKE N'%'+NCHAR(536)+'%'
This will help you find the symbol even if it was inserted as an unknown symbol like in the first insert below.
DECLARE @Table TABLE (text nvarchar(50))
INSERT INTO @Table(text)
SELECT 'Ș'
UNION ALL
SELECT N'Ș'
SELECT UNICODE(text) UNICODE
FROM @Table
Results:
UNICODE
63
536
'Ş' is NCHAR (536) and 'ş' is NCHAR (537). If you do this:
SELECT * FROM @Table WHERE text LIKE N'%'+NCHAR(536)+'%'
Results:
text
?
Ș
source to share