CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
ProgrammingLlama
ProgrammingLlama

Reputation: 38767

SQL string equality comparison with EXCEPT

I've just realised that there is a problem with a couple of triggers I have written which can be demonstrated with this query here:

SELECT CASE WHEN 'TEST' = 'TEST ' THEN 'y' ELSE 'n' END

Whilst you might think that this resolves to N, it in fact resolves to Y as SQL Server trims the trailing spaces from strings before comparing them in this way.

One suggestion appears to be to use like, which is fine in a where clause:

SELECT CASE WHEN 'TEST' LIKE 'TEST ' THEN 'y' ELSE 'n' END does indeed resolve to n as you would hope.

Another suggestion is to check the length too, using `DATALENGTH:

SELECT CASE WHEN 'TEST' = 'TEST ' AND DATALENGTH('TEST') = DATALENGTH('TEST ') THEN 'y' ELSE 'n' END

Unfortuantely, this is going to cause extra complexity in a trigger I use to update another table:

SELECT
    FullName COLLATE SQL_Latin1_General_CP1_CS_AS
FROM TableA
EXCEPT
SELECT
    FullName COLLATE SQL_Latin1_General_CP1_CS_AS
FROM TableB

Which then becomes:

SELECT
    FullName COLLATE SQL_Latin1_General_CP1_CS_AS,
    FullName_Length = DATALENGTH(FullName)
FROM TableA
EXCEPT
SELECT
    FullName COLLATE SQL_Latin1_General_CP1_CS_AS,
    FullName_Length = DATALENGTH(FullName)
FROM TableB

Is there a simpler way to do this? Is there some way I can tell EXCEPT to compare strings including their length rather than by trimming the trailing characters?

Apologies if this has been asked before, I haven't been very lucky with finding anything specifically looking at EXCEPT.

Upvotes: 2

Views: 483

Answers (1)

gbn
gbn

Reputation: 432271

Well, you could add something at the end

SELECT 
      CASE WHEN CONCAT('TEST', 'ADummyValue') = 
             CONCAT('TEST      ', 'ADummyValue') THEN 'y' ELSE 'n' END


SELECT
    CONCAT(FullName, 'ADummyValue') AS FullName COLLATE SQL_Latin1_General_CP1_CS_AS
FROM TableA
EXCEPT
SELECT
    CONCAT(FullName, 'ADummyValue') COLLATE SQL_Latin1_General_CP1_CS_AS
FROM TableB

Using REVERSE

SELECT
    REVERSE(FullName) AS FullName COLLATE SQL_Latin1_General_CP1_CS_AS
FROM TableA
EXCEPT
SELECT
    REVERSE(FullName) COLLATE SQL_Latin1_General_CP1_CS_AS
FROM TableB

Upvotes: 3

Related Questions