0

I can't deal with an Arabic character 'ـ' SHIFT+J from keyboard, with ASCII value of 220 char(220) and UNICODE value of U+0640 .. in SQL Server nor in SELECT nor REPLACE or any other functions

I tried all possible Arabic collation

Arabic_CS_AS, Arabic_CI_AI, Arabic_CS_AI, Arabic_CI_AS

I tried this but did not solve my issue

SELECT        ColumnName
FROM            dbo.TableName
where 
cast(ColumnName as nvarchar) like cast( N'%' + char(220) + N'%' as nvarchar)
Mariam
  • 533
  • 2
  • 12
  • 22
  • Does this answer your question? [What is the difference between char, nchar, varchar, and nvarchar in SQL Server?](https://stackoverflow.com/questions/176514/what-is-the-difference-between-char-nchar-varchar-and-nvarchar-in-sql-server) – demo Apr 27 '21 at 11:16
  • no i tried this SELECT ColumnName FROM dbo.TableName where cast(ColumnName as nvarchar) like cast( N'%' + char(220) + N'%' as nvarchar) but did not solve my issue – Mariam Apr 27 '21 at 11:39
  • 3
    Sample data, and expected results, will really help us help you here. – Thom A Apr 27 '21 at 11:41
  • I mean different thing... As I understand you save Arabic character (unicoded character) in column that doesn't support unicode. So values in that table aren't correct... – demo Apr 27 '21 at 11:42
  • Also declaring a data type without it's explicit length, precision, scale, etc, is a *very* bad habit. Always make sure you declare them. `cast(ColumnName as nvarchar)` could easily be causing implicit truncation. – Thom A Apr 27 '21 at 11:42
  • [U+640](https://www.fileformat.info/info/unicode/char/0640/index.htm) will only be `char(220)` in code page [Windows-1256](https://en.wikipedia.org/wiki/Windows-1256) and will very much depend on the database's default collation. Have you tried using `nchar(1600)` instead? – AlwaysLearning Apr 27 '21 at 12:17
  • Try `NCHAR(640)` instead of `CHAR(640)` with no `convert`. and you should always specify n/varchar lengths – Charlieface Apr 27 '21 at 14:43

0 Answers0