CODEWITHSUNDEEP
sqlsql-serverdatabaserelational-databaseascii
user7491692
user7491692

Reputation:

Transferring ASCII (sub)set to table

I am trying to transfer a subset of ASCII characters to a table but I keep getting an error saying I am duplicating values in SSMS.

This is my code for the table:

create table xyz(
  aChar char not null,
  primary key(aChar)
);

and this to populate the table:

declare @xChars int = 250
declare @iterations int = 0
while @iterations < @xChars
begin
insert into xyz values (char(@iterations))
set @iterations += 1
end

Hopefully one of you can help me out here.

Upvotes: 1

Views: 46

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

Character data in SQL Server is stored with a collation. A collation defines the sort order and equality comparison for text. The default collation is case-insensitive, so the character 'a' and the character 'A' compare as equal. You have a unique index on aChar, so you can't store 'a' and 'A' in the column. There also may by whitespace or non-printable characters that will compare equal.

You want to declare that column with a binary collation so that the characters compare by their code point, not by a linguistic collation. eg

drop table if exists xyz
create table xyz(
  aChar char  collate Latin1_General_BIN2 not null,
  primary key(aChar)
);
go
declare @xChars int = 250
declare @iterations int = 0
while @iterations < @xChars
begin
insert into xyz values (char(@iterations))
set @iterations += 1
end

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

The problem is a case-insensitive collation. 'a' and 'A' are the same thing. So, use a case sensitive collation:

create table xyz (
  aChar char collate SQL_Latin1_General_CP1_CS_AS not null,
  primary key(aChar)
);

You can do this with a single statement:

with nums as (
      select 1 as n
      union all
      select n + 1
      from nums
      where n + 1 < 250
     )
insert into xyz (aChar)
    select char(nums.n)
    from nums
options (maxrecursion 0);

Here is a SQL Fiddle.

You can also do this using a computed column:

create table xyz(
  aChar_num smallint not null,
  aChar as (char(aChar_num)),
  primary key(aChar_num)
);

with nums as (
      select 1 as n
      union all
      select n + 1
      from nums
      where n + 1 < 250
     )
insert into xyz (aChar_num)
    select nums.n
    from nums
    option (maxrecursion 0);

As shown in this SQL Fiddle.

Upvotes: 2

Related Questions