CODEWITHSUNDEEP
t-sqlsql-server-2005
Wayne Molina
Wayne Molina

Reputation: 19586

NULLIF check for empty string returns empty string with a column name, but NULL with the column value

I have a database column set to char(255) (yes, CHAR. Don't ask me why that's how the database was set up) that at present has an empty string with two spaces (i.e. " "). Using NULLIF(LTRIM(RTRIM(column_name)), '') does NOT work (the output is [two empty spaces]). However, using NULLIF(' ', '') works correctly and the output is NULL. In other words, the actual column value works correctly, while passing the name of the column returns an incorrect value.

Any ideas on this?

Upvotes: 1

Views: 1341

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

I believe the column must have more than just spaces. For example:

CREATE TABLE #x(id INT, y CHAR(255));

INSERT #X SELECT 1, ' '
UNION ALL SELECT 2, '  '
UNION ALL SELECT 3, ' ' + CHAR(9);

SELECT id, NULLIF(LTRIM(RTRIM(y)),'') FROM #x;

Results:

1   NULL
2   NULL
3

For a row where this fails, try this:

DECLARE @s CHAR(255);
SELECT @s = y FROM #x WHERE id = 3;

DECLARE @i INT;
SET @i = 1;
WHILE @i <= DATALENGTH(@s)
BEGIN
  IF ASCII(SUBSTRING(@s, @i, 1)) <> 32
  BEGIN
    PRINT 'Position ' + RTRIM(@i) + ' = CHAR(' 
          + RTRIM(ASCII(SUBSTRING(@s, @i, 1))) + ')';
  END
  SET @i = @i + 1;
END

It should tell you what other characters are in there, and where.

Upvotes: 3

Related Questions