0

I have a column Name nvarchar(100) and some of the records contain some

strange encoding, example:

׳—׳•׳–׳¨ ׳ž׳ ׳›׳œ ׳“׳¦׳ž׳‘׳¨ 2019 ׳׳ž׳•׳× ׳ž׳™׳“׳” ׳œ׳ ׳™׳”׳•׳œ ׳”׳¨׳©׳•׳ž׳” ׳”׳¨׳₪׳•׳׳™׳×.pdf

and I want to Convert Or Set those records to Hebrew encoding,

how can I do that ?

My current collection is Hebrew_CI_AI

EDIT this answer suggested by @Igor only give the syntax to change collection but I need to know to what collection to change the column so I will get plain Hebrew

Ben.S
  • 708
  • 1
  • 5
  • 24
  • What you are looking for is called [Collation](https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15). – Igor Jun 15 '21 at 14:02
  • Does this answer your question? [Cast collation of nvarchar variables in t-sql](https://stackoverflow.com/questions/1631936/cast-collation-of-nvarchar-variables-in-t-sql) – Igor Jun 15 '21 at 14:12
  • 2
    The data is already corrupted in your DB. SQL Server cannot recode arbitrary mojibake. Ensure that when the data is stored it passes the data as a Unicode character string, in whatever way this is supposed to be done by the underlying data access library. A common mistake for libraries/clients is to use UTF-8 as an encoding for Unicode and then pass those strings into SQL Server as if it directly supports UTF-8 -- this is only true for SQL Server 2019+ and then only for `VARCHAR` with an appropriate collation, not `NVARCHAR`. – Jeroen Mostert Jun 15 '21 at 14:16
  • @Igor to what collection should I change the column so I will get the text in regular Hebrew ? I already know the syntax for changing the column collection – Ben.S Jun 15 '21 at 14:24
  • @JeroenMostert I don't think that the data is corrupted because if I put the text in here I enter the text here http://www.pixiesoft.com/flip/ and press the red button I get the correct words in Hebrew, I need something like that in sql server – Ben.S Jun 15 '21 at 14:27
  • 3
    Sorry, I can't read Hebrew so I have no idea what kind of recoding step that site performs. I do know that whatever it does is probably not something SQL Server can do out of the box -- it has almost no ability to recode characters when the encoding is not what it should be. The data is corrupted in the sense that if it was not and the characters were encoded as UTF-16 (which `NVARCHAR` requires) they would be displayed correctly. Changing the collation is useless, in any case -- this *only* dictates encoding for `VARCHAR` columns, *not* `NVARCHAR`. For `NVARCHAR` it only dictates comparison. – Jeroen Mostert Jun 15 '21 at 14:34
  • @JeroenMostert minor nitpick... SQL Server itself regards `nchar`, `nvarchar` and `ntext` as UCS-2 encoding unless the user database is using an `_SC` Supplementary Characters collation, at which point it switches out all of the string handling functions to be UTF-16 aware. While applications can read and write UTF-16 data in a UCS-2 database, SQL Server's string handling functions will return incorrect results in the presence of supplementary characters. – AlwaysLearning Jun 15 '21 at 21:39

0 Answers0