0

I'm racking my brains how to insert Polish characters into varchar column with SQL_Latin1_General_CP1_CI_AS collation. I've tried using collate Polish_CI_AS with insert statement, convert function, N'my_text' but to no avail. Is there any kind of workaround to get it inserted without changing the collation of a column (which requires recreating the table)?

drop table if exists #MyTable
create table #MyTable (MyColumn varchar(255) collate SQL_Latin1_General_CP1_CI_AS)

insert into #MyTable
(MyColumn)
select 'śmieci' 

insert into #MyTable
(MyColumn)
select 'śmieci' collate Polish_CI_AS

insert into #MyTable
(MyColumn)
select N'śmieci' 

insert into #MyTable
(MyColumn)
select convert(nvarchar(255),'śmieci')
Arkadiusz
  • 369
  • 5
  • 18
  • 2
    You can't. That's the whole problem with codepages. Why use `varchar` at all? That's just asking for trouble. SQL Server always supported Unicode, which *just works* as your own question proves - Stack Overflow is a .NET web app storing posts in `nvarchar` fields in SQL Server – Panagiotis Kanavos Jun 13 '23 at 07:47
  • 2
    `N'my_text' but to no avail` because the field itself is not Unicode and uses the wrong codepage. Any non-Latin1 characters stored there will get mangled. The solution is to use `nvarchar`. If you have several millions of rows and worry about size you can use table compression. It's transparent, available in all versions and editions and can actually improve performance by reducing IO – Panagiotis Kanavos Jun 13 '23 at 07:50
  • 2
    `which requires recreating the table` no. All you need to do is `ALTER COLUMN` to change the type or collation. Collations are stored per column, not per table – Panagiotis Kanavos Jun 13 '23 at 07:51
  • 1
    Check this related question too [Change datatype varchar to nvarchar in existing SQL Server 2005 database. Any issues?](https://stackoverflow.com/questions/8157602/change-datatype-varchar-to-nvarchar-in-existing-sql-server-2005-database-any-is) – Panagiotis Kanavos Jun 13 '23 at 07:53

0 Answers0