Enforcing distinct number of trailing spaces

Question

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 '/'.

Answer 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', ' ');

• Lawrence Anderson: 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)

Answer 2

Another idea, similar to Joe Obbish's answer. It, too, requires that the code be defined as VARCHAR.

It calculates the length of the string using either LEN(code + 'X') or more simply DATALENGTH(code) (thnx to @PaulWhite):

CREATE TABLE T226714 (
 code_type CHAR(4) NOT NULL,
 code VARCHAR(30) NOT NULL,
 -- code_len AS len(code + 'X') - 1
 code_len AS datalength(code) 
);
CREATE UNIQUE INDEX UI ON T226714 (code_type, code, code_len);
GO

✓

-- works
INSERT INTO T226714 VALUES ('cha', ' ');
INSERT INTO T226714 VALUES ('cha', '');
GO

2 rows affected

-- can't insert this as a third row due to the unique index:
INSERT INTO T226714 VALUES ('cha', ' ');
GO

Msg 2601 Level 14 State 1 Line 2
Cannot insert duplicate key row in object 'dbo.T226714' 
with unique index 'UI'. The duplicate key value is (cha , , 1).
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.

-- can't insert this as a third row due to the unique index:
INSERT INTO T226714 VALUES ('cha', '');
GO

Msg 2601 Level 14 State 1 Line 2
Cannot insert duplicate key row in object 'dbo.T226714' 
with unique index 'UI'. The duplicate key value is (cha , , 0).
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.

select * from T226714 ;
GO

code_type | code | code_len
:-------- | :--- | -------:
cha | | 0
cha | | 1

db<>fiddle here

Answer 3

While I am not usually a fan of triggers, you can use an INSTEAD OF insert trigger to prevent adding the new record to the table. The padding behaviour does not apply to the LIKE operator, so you can check for existing rows LIKE the inserted row, with no wildcards, to check for the existing row and rollback the insert if found.

This has the advantage of not needing to alter your table design, particularly useful if you have any SELECT * in your code (which I'm sure you don't :) ). It may have negative impacts on performance if this table has a lot of inserts\updates.

Setup:

IF (SELECT OBJECT_ID('BlankTest')) IS NOT NULL
BEGIN
 DROP TABLE BlankTest
END
CREATE TABLE BlankTest (ID INT IDENTITY, CharData VARCHAR(3))
GO
CREATE TRIGGER trgInsert ON BlankTest
INSTEAD OF INSERT
AS
BEGIN
 IF NOT EXISTS (
 SELECT *
 FROM BlankTest
 WHERE CharData LIKE (SELECT CharData FROM inserted) -- = would encounter the padding behaviour. LIKE with no wildcards performs a pseudo-equality check that does not pad.
 )
 BEGIN
 INSERT INTO BlankTest (CharData)
 SELECT CharData
 FROM inserted
 END
 ELSE
 BEGIN
 PRINT 'Existing value found, prevented insert.'
 END
END
GO

Testing:

INSERT INTO BlankTest (CharData)
VALUES (' '), ('1'), ('')
SELECT * FROM BlankTest -- Returns 3 rows
GO
INSERT INTO BlankTest (CharData) -- Fails. Check the messages tab for the printed output. Could raise an error/trigger rollback in legitimate code.
VALUES (' ')
SELECT * FROM BlankTest -- Returns original 3 rows
GO