SQL Server Cannot find letter 'ș' or 'Ș' inserted from Romanian (Standard) keyboard

cczfrluj 于 2023-08-02 发布在其他

关注(0)|答案(3)|浏览(110)

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 'Ş'

sql-server

来源：https://stackoverflow.com/questions/30187221/cannot-find-letter-%c8%99-or-%c8%98-inserted-from-romanian-standard-keyboard

3条答案

按热度按时间

vwoqyblh1#

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:

replace only matches the beginning of the string

赞(0）回复(0）举报 2023-08-02

e4yzc0pl2#

WHERE columnname LIKE N'%'+NCHAR(536)+'%'

This should help you find the character even if it was inserted as an unknown character as 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 then do:

SELECT * FROM @Table WHERE text LIKE N'%'+NCHAR(536)+'%'

Results:

text
?
Ș

赞(0）回复(0）举报 2023-08-02

i2byvkas3#

Letter 'ș' or 'Ș' comes from Romanian (Standard) keyboard, however these are UTF-16 characters. Refer this link . These characters are supported only in SQL server version 2019 & above. There ascii value always comes out as 63 which is wrong. Ascii 63 value is question mark(?) value.

However similar look like characters 'ş' and 'Ş' are supported in each version of SQL. As you can see there a slight difference between supported and unsupported characters. The difference is, unsupported character like 'ș' has dot in it and there is a gap between s character and dot. However it's similar character 'ş' has no gap. Now I am demonstrating it using SQL query.

select cast('ş' as nvarchar) -- output s

select cast('ș' as nvarchar) -- output ?

Screenshot of query execution

The only solution that I can suggest here is that just either replace unsupported characters with supported one or switch to SQL version 2019 & above. I hope this would help you.

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Cannot find letter 'ș' or 'Ș' inserted from Romanian (Standard) keyboard

3条答案

相关问题

热门标签

最新问答