-1

So trying to remove all the extended ascii characters and used collation SQL_Latin1_General_CP1253_CI_AI in the ddl but still getting ascii characters. Is there any suggestion ?

I have a value stored in the sql as 'àccõrd' and i want it to be stored as accord. When i try select àccõrd collate SQL_Latin1_General_CP1253_CI_AI it works but when i load it still gets loaded as àccõrd

Ahmad KAmolov
  • 35
  • 3
  • Please provide a [mcve] and a full explanation of what are you doing and what the difficulty is – Charlieface Apr 29 '21 at 20:07
  • 1
    If you're trying to remove non-ASCII characters why is it a problem to still have ASCII characters? Please edit your question with some example data, the code you're trying to use, the actual results and your expected results. – AlwaysLearning Apr 30 '21 at 12:46

1 Answers1

0

Here are two ways to achieve what you're trying to do...

First method: define the column with a specific collation, e.g.:

create table dbo.Foo (
  FooName varchar(50) collate SQL_Latin1_General_CP1253_CI_AI
);
insert dbo.Foo (FooName) values ('àccõrd');
select FooName from dbo.Foo;

Which yields:

FooName
-------
accord

Second method: collate the text when inserting it into your table:

-- Display the SQL Server instance's "default collation," configured during setup.
-- e.g.: SQL_Latin1_General_CP1_CI_AS
select serverproperty('collation') as ServerCollation;

-- Display the current database's "default collation," configured in CREATE DATABASE.
-- e.g.: SQL_Latin1_General_CP1_CI_AS
select collation_name
from sys.databases
where [name]=db_name();

create table dbo.Bar (
  BarName varchar(50) --database default: collate SQL_Latin1_General_CP1_CI_AS
);
insert dbo.Bar (BarName) values ('àccõrd' collate SQL_Latin1_General_CP1253_CI_AI);
select BarName from dbo.Bar;

Which yields:

BarName
-------
accord

NOTE: These collation tricks only work with char and varchar data types.

AlwaysLearning
  • 7,915
  • 5
  • 27
  • 35