Ygthkb

How to get the first element while continue streaming?

Formatting a table to look nice

Would the melodic leap of the opening phrase of Mozart's K545 be considered dissonant?

Difference between 'stomach' and 'uterus'

How to fix my table, centering of columns

When do _WA_Sys_ statistics Get Updated?

Called into a meeting and told we are being made redundant (laid off) and "not to share outside". Can I tell my partner?

Where is the fallacy here?

How do I deal with being envious of my own players?

Why doesn't "adolescent" take any articles in "listen to adolescent agonising"?

Do AL rules let me pick different starting equipment?

I can't die. Who am I?

Why is my Contribution Detail Report (native CiviCRM Core report) not accurate?

When to use mean vs median

Why is it "take a leak?"

Is there a way to find out the age of climbing ropes?

How do you say “my friend is throwing a party, do you wanna come?” in german

Is there a math equivalent to the conditional ternary operator?

Why did the Cray-1 have 8 parity bits per word?

Inconsistent behaviour between dict.values() and dict.keys() equality in Python 3.x and Python 2.7

Is there a full canon version of Tyrion's jackass/honeycomb joke?

PTIJ: What’s wrong with eating meat and couscous?

Lock enemy's y-axis when using Vector3.MoveTowards to follow the player

Must 40/100G uplink ports on a 10G switch be connected to another switch?

Enforcing distinct number of trailing spaces

Enforcing database integritysysadmin cannot drop login when ddl audit trigger enabledHow can I enforce data integrity in this model with a M:2 relationship?Trim whitespace (spaces, tabs, newlines)How does one investigate the performance of a BULK INSERT statement?Something similar to GENERATED ALWAYS for a DEFAULT? (SQL Server / Azure SQL)Forcing Flow DistinctSame query way faster with distinct than without distinctfloat to varchar with no trailing zerosDesign of portable tables with validity interval (historization, temporal databases)

2

0

I'm working on converting a SQL Anywhere (ASA) 5.5 based application and its database to Microsoft SQL Server.

Existing is a table with a primary key clustered on two columns, code_type char(4) and code char(30). I was able to build the table fine, but attempting to move over the data I ran into a conflict because the table contained ('cha', ' ') and ('cha', '') and speaking with another developer it sounds like this has a use case. Not really looking to retrain the users, what options are there to enforce a distinct number of spaces at the database level?

The database is for a single application so in the end I can rely on control of how data is inserted into the table but would like to enforce an intended distinction in the table definition.

If ('cha', '') exists in the table, I'd like to be able to insert ('cha', ' ') as it has more trailing spaces, but not ('cha', '') because it has an equal number of spaces (0) as an existing entry.

This question on UNIQUE Index outlines the behavior on unique index, SQL is applying right padding of spaces to make two strings equally sized before making the comparison per ANSI/ISO SQL-92 Section Section 8.2 General Rule #3. I've confirmed this behavior with the char type as the link used varchar. I'm not committed to using a key, just some way to enforce a distinct number of trailing spaces.

There are quite a few characters guaranteed not to show up in the second column. The field is either letter abbreviations or legal document citations. The legal document citations seem to be contain alphanumeric characters plus '(', ')', '-', '.', ' ', and '/'.

sql-server sql-server-2014 constraint

share|improve this question

edited 5 mins ago

Paul White♦

52.9k14281457

asked Jan 9 at 18:59

Lawrence AndersonLawrence Anderson

132

add a comment | 

2

0

I'm working on converting a SQL Anywhere (ASA) 5.5 based application and its database to Microsoft SQL Server.

Existing is a table with a primary key clustered on two columns, code_type char(4) and code char(30). I was able to build the table fine, but attempting to move over the data I ran into a conflict because the table contained ('cha', ' ') and ('cha', '') and speaking with another developer it sounds like this has a use case. Not really looking to retrain the users, what options are there to enforce a distinct number of spaces at the database level?

The database is for a single application so in the end I can rely on control of how data is inserted into the table but would like to enforce an intended distinction in the table definition.

If ('cha', '') exists in the table, I'd like to be able to insert ('cha', ' ') as it has more trailing spaces, but not ('cha', '') because it has an equal number of spaces (0) as an existing entry.

This question on UNIQUE Index outlines the behavior on unique index, SQL is applying right padding of spaces to make two strings equally sized before making the comparison per ANSI/ISO SQL-92 Section Section 8.2 General Rule #3. I've confirmed this behavior with the char type as the link used varchar. I'm not committed to using a key, just some way to enforce a distinct number of trailing spaces.

There are quite a few characters guaranteed not to show up in the second column. The field is either letter abbreviations or legal document citations. The legal document citations seem to be contain alphanumeric characters plus '(', ')', '-', '.', ' ', and '/'.

sql-server sql-server-2014 constraint

share|improve this question

edited 5 mins ago

Paul White♦

52.9k14281457

asked Jan 9 at 18:59

Lawrence AndersonLawrence Anderson

132

add a comment | 

I'm working on converting a SQL Anywhere (ASA) 5.5 based application and its database to Microsoft SQL Server.

Existing is a table with a primary key clustered on two columns, code_type char(4) and code char(30). I was able to build the table fine, but attempting to move over the data I ran into a conflict because the table contained ('cha', ' ') and ('cha', '') and speaking with another developer it sounds like this has a use case. Not really looking to retrain the users, what options are there to enforce a distinct number of spaces at the database level?

The database is for a single application so in the end I can rely on control of how data is inserted into the table but would like to enforce an intended distinction in the table definition.

If ('cha', '') exists in the table, I'd like to be able to insert ('cha', ' ') as it has more trailing spaces, but not ('cha', '') because it has an equal number of spaces (0) as an existing entry.

This question on UNIQUE Index outlines the behavior on unique index, SQL is applying right padding of spaces to make two strings equally sized before making the comparison per ANSI/ISO SQL-92 Section Section 8.2 General Rule #3. I've confirmed this behavior with the char type as the link used varchar. I'm not committed to using a key, just some way to enforce a distinct number of trailing spaces.

There are quite a few characters guaranteed not to show up in the second column. The field is either letter abbreviations or legal document citations. The legal document citations seem to be contain alphanumeric characters plus '(', ')', '-', '.', ' ', and '/'.

sql-server sql-server-2014 constraint

share|improve this question

edited 5 mins ago

Paul White♦

52.9k14281457

asked Jan 9 at 18:59

Lawrence AndersonLawrence Anderson

132

share|improve this question

edited 5 mins ago

Paul White♦

52.9k14281457

asked Jan 9 at 18:59

Lawrence AndersonLawrence Anderson

132

share|improve this question

edited 5 mins ago

Paul White♦

52.9k14281457

asked Jan 9 at 18:59

Lawrence AndersonLawrence Anderson

132

edited 5 mins ago

Paul White♦

52.9k14281457

edited 5 mins ago

Paul White♦

52.9k14281457

asked Jan 9 at 18:59

Lawrence AndersonLawrence Anderson

132

asked Jan 9 at 18:59

Lawrence AndersonLawrence Anderson

132

1 Answer
1

active

oldest

votes

1

I wouldn't call this a good solution but it seems to work. You can create a computed column with an appended character and create a unique index on code_type and the computed column. For the appended character you can use a character which will never appear in your data or any string of at least 31 character length if you'd like to avoid making assumptions like that. It's probably easiest to go through an example.

This is the problem that you're having:

CREATE TABLE dbo.T226714 (

    code_type CHAR(4) NOT NULL,

    code VARCHAR(30) NOT NULL,

    PRIMARY KEY (code_type, code)

);



-- fails due to PK error

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

INSERT INTO dbo.T226714 VALUES ('cha', '');

Instead, do something like this:

DROP TABLE dbo.T226714;



CREATE TABLE dbo.T226714 (

    code_type CHAR(4) NOT NULL,

    code VARCHAR(30) NOT NULL,

    code_with_end_char AS CAST(code + 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ' AS VARCHAR(61))

);



CREATE UNIQUE INDEX UI ON dbo.T226714 (code_type, code_with_end_char);



-- works

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

INSERT INTO dbo.T226714 VALUES ('cha', '');



-- can't insert this as a third row due to the unique index:

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

share|improve this answer

answered Jan 10 at 14:54

Joe ObbishJoe Obbish

21.2k33086

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That works for me. I will note that for the sake of thoroughness having [code] as data type varchar rather than char is required for this solution. With all columns as char duplicate key issue error (cha , ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ)
  
  – Lawrence Anderson
  Jan 10 at 16:43
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f226714%2fenforcing-distinct-number-of-trailing-spaces%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

1

I wouldn't call this a good solution but it seems to work. You can create a computed column with an appended character and create a unique index on code_type and the computed column. For the appended character you can use a character which will never appear in your data or any string of at least 31 character length if you'd like to avoid making assumptions like that. It's probably easiest to go through an example.

This is the problem that you're having:

CREATE TABLE dbo.T226714 (

    code_type CHAR(4) NOT NULL,

    code VARCHAR(30) NOT NULL,

    PRIMARY KEY (code_type, code)

);



-- fails due to PK error

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

INSERT INTO dbo.T226714 VALUES ('cha', '');

Instead, do something like this:

DROP TABLE dbo.T226714;



CREATE TABLE dbo.T226714 (

    code_type CHAR(4) NOT NULL,

    code VARCHAR(30) NOT NULL,

    code_with_end_char AS CAST(code + 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ' AS VARCHAR(61))

);



CREATE UNIQUE INDEX UI ON dbo.T226714 (code_type, code_with_end_char);



-- works

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

INSERT INTO dbo.T226714 VALUES ('cha', '');



-- can't insert this as a third row due to the unique index:

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

share|improve this answer

answered Jan 10 at 14:54

Joe ObbishJoe Obbish

21.2k33086

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That works for me. I will note that for the sake of thoroughness having [code] as data type varchar rather than char is required for this solution. With all columns as char duplicate key issue error (cha , ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ)
  
  – Lawrence Anderson
  Jan 10 at 16:43
  

  
  
  
  

add a comment | 

1

I wouldn't call this a good solution but it seems to work. You can create a computed column with an appended character and create a unique index on code_type and the computed column. For the appended character you can use a character which will never appear in your data or any string of at least 31 character length if you'd like to avoid making assumptions like that. It's probably easiest to go through an example.

This is the problem that you're having:

CREATE TABLE dbo.T226714 (

    code_type CHAR(4) NOT NULL,

    code VARCHAR(30) NOT NULL,

    PRIMARY KEY (code_type, code)

);



-- fails due to PK error

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

INSERT INTO dbo.T226714 VALUES ('cha', '');

Instead, do something like this:

DROP TABLE dbo.T226714;



CREATE TABLE dbo.T226714 (

    code_type CHAR(4) NOT NULL,

    code VARCHAR(30) NOT NULL,

    code_with_end_char AS CAST(code + 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ' AS VARCHAR(61))

);



CREATE UNIQUE INDEX UI ON dbo.T226714 (code_type, code_with_end_char);



-- works

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

INSERT INTO dbo.T226714 VALUES ('cha', '');



-- can't insert this as a third row due to the unique index:

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

share|improve this answer

answered Jan 10 at 14:54

Joe ObbishJoe Obbish

21.2k33086

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That works for me. I will note that for the sake of thoroughness having [code] as data type varchar rather than char is required for this solution. With all columns as char duplicate key issue error (cha , ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ)
  
  – Lawrence Anderson
  Jan 10 at 16:43
  

  
  
  
  

add a comment | 

I wouldn't call this a good solution but it seems to work. You can create a computed column with an appended character and create a unique index on code_type and the computed column. For the appended character you can use a character which will never appear in your data or any string of at least 31 character length if you'd like to avoid making assumptions like that. It's probably easiest to go through an example.

This is the problem that you're having:

CREATE TABLE dbo.T226714 (

    code_type CHAR(4) NOT NULL,

    code VARCHAR(30) NOT NULL,

    PRIMARY KEY (code_type, code)

);



-- fails due to PK error

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

INSERT INTO dbo.T226714 VALUES ('cha', '');

Instead, do something like this:

DROP TABLE dbo.T226714;



CREATE TABLE dbo.T226714 (

    code_type CHAR(4) NOT NULL,

    code VARCHAR(30) NOT NULL,

    code_with_end_char AS CAST(code + 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ' AS VARCHAR(61))

);



CREATE UNIQUE INDEX UI ON dbo.T226714 (code_type, code_with_end_char);



-- works

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

INSERT INTO dbo.T226714 VALUES ('cha', '');



-- can't insert this as a third row due to the unique index:

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

share|improve this answer

answered Jan 10 at 14:54

Joe ObbishJoe Obbish

21.2k33086

CREATE TABLE dbo.T226714 (

    code_type CHAR(4) NOT NULL,

    code VARCHAR(30) NOT NULL,

    PRIMARY KEY (code_type, code)

);



-- fails due to PK error

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

INSERT INTO dbo.T226714 VALUES ('cha', '');

DROP TABLE dbo.T226714;



CREATE TABLE dbo.T226714 (

    code_type CHAR(4) NOT NULL,

    code VARCHAR(30) NOT NULL,

    code_with_end_char AS CAST(code + 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ' AS VARCHAR(61))

);



CREATE UNIQUE INDEX UI ON dbo.T226714 (code_type, code_with_end_char);



-- works

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

INSERT INTO dbo.T226714 VALUES ('cha', '');



-- can't insert this as a third row due to the unique index:

INSERT INTO dbo.T226714 VALUES ('cha', ' ');

share|improve this answer

answered Jan 10 at 14:54

Joe ObbishJoe Obbish

21.2k33086

answered Jan 10 at 14:54

Joe ObbishJoe Obbish

21.2k33086

answered Jan 10 at 14:54

Joe ObbishJoe Obbish

21.2k33086