Transact SQL - Compare Null and Blank (empty space

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Transact SQL - Compare Null and Blank (empty space

Author

Topic

Sandeidsbuen
Starting Member

6 Posts

Posted - 2012-09-21 : 12:15:38

Hello,

I have two tables; TABLE1 and TABLE2.
Both with columns: line_01, line_02, line_03, line_04 and line_05.

I want to list a result when line_01 has the same data in both tables and when either line_04 or line_05 has different data in the two tables.

This works fine when there is text or numbers in the columns.

However, some columns does not have data. In TABLE1 this is shown as "NULL" and in TABLE2 as '' (empty space/blank).

When I check for differences; I do not want to list the cases where TABLE1 has NULL and TABLE2 is blank (In my case this is the same data).

I have used this code with or without the isnull function without luck. When TABLE1.line_05 = NULL and TABLE2.line_05 is blank the row is listed.

SELECT

'TAB2-line_01' = TABLE2.line_01,
'TAB1-line_01' = TABLE1.line_01,
'TAB2-line_04' = TABLE2.line_04,
'TAB1-line_04' = TABLE1.line_04,
'TAB2-line_05' = TABLE2.line_05,
'TAB1-line_05' = TABLE1.line_05

FROM TS_NAMEPLATE TABLE2

JOIN TS_NAMEPLATE_MAIN TABLE1 ON TABLE1.line_01 = TABLE2.line_01

WHERE UPPER(TABLE1.complete) = 'YES' AND (isnull(TABLE2.line_04,'') <> isnull(TABLE1.line_04,'') OR
isnull(TABLE2.line_05,'') <> isnull(TABLE1.line_05,''))

AND TABLE2.line_01 IN ('43 LSHH-55655')

The result is as follow:
TAB2-line_01 = 43 LSHH-55655
TAB1-line_01 = 43 LSHH-55655
TAB2-line_04 = 35 15525
TAB1-line_04 = 35 15525
TAB2-line_05 = (blank)
TAB1-line_05 = NULL

I know I can change/convert all blanks in TABLE2 to NULL and then it will work.
However, does anyone know if the isnull / nullif or simillar functions can be used?

Sandeidsbuen

Best Regards,

Sandeidsbuen

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 12:36:28

Your query does seem like it should work as you expect. Are the nulls really nulls or do they contain the string "NULL"? You can check using

SELECT * FROM TABLE1 WHERE line_05 = 'NULL';

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-21 : 12:39:39

if you dont want NULL,'' differences you can just remove ISNULL logic from where and it should work fine under default conditions (ANSI NULL settings)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 13:00:34

Visakh, if you omit the ISNULL's it seems like that would not do what Sandeidsbuen is looking for, for example in cases where one side is null and the other is a non-empty string?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-21 : 13:25:02

quote:
Originally posted by sunitabeck

Visakh, if you omit the ISNULL's it seems like that would not do what Sandeidsbuen is looking for, for example in cases where one side is null and the other is a non-empty string?

op wanted to ignore them isnt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 13:33:02

I read it as "if one side is null and the other side is an empty string consider them being equal", and so I interpreted that to mean that if one side is null and the other side is a non-empty string consider that as being different and hence to be pulled in by the query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-21 : 13:41:43

i went by this

I do not want to list the cases where TABLE1 has NULL and TABLE2 is blank

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sandeidsbuen
Starting Member

6 Posts

Posted - 2012-09-21 : 13:46:46

Sunitabeck; The nulls are really nulls, because your code returned no results, however this one did: SELECT * FROM TABLE1 WHERE line_05 IS NULL.

Visakh16; as I wrote in my original post I've tried with or without the "isnull logic" - same result.

If I have not been clear, I'm sorry for that. What I want is to list all the rows where line_04 or line_05 is different in the two tables. However, I do not want to list the difference between NULL and ''. These I will treat as equal. If in table1; line_04 is NULL and in table2 line_04 is '35 25896' then of course I want to list this. I only want to disregard the NULL / '' differences.

I don't know if that was any clearer ;-)

Best Regards,

Sandeidsbuen

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 13:58:11

Another possibility is that what you see as blanks may not necessarily be empty strings or spaces, it could be some other white-space character such as tab. See the example below:

CREATE TABLE #tmpA (id INT, a VARCHAR(32));
CREATE TABLE #tmpB (id INT, b VARCHAR(32));

INSERT INTO #tmpA VALUES (1,'a'),(2,NULL),(3,''), (4,NULL);
INSERT INTO #tmpB VALUES (1,NULL),(2,''), (3,'abcd'), (4, CHAR(9));


SELECT * FROM #tmpA a INNER JOIN #tmpB b 
ON  a.id = b.id
WHERE a <> b

SELECT * FROM #tmpA a INNER JOIN #tmpB b 
ON  a.id = b.id
WHERE ISNULL(a,'') <> ISNULL(b,'');

DROP TABLE #tmpA;
DROP TABLE #tmpB;

If you can identify such columns that show up in your query when they should not, you can examine the content by casting them to varbinary. In #tmpB in the above example, you would do the following:

SELECT CAST(b AS VARBINARY) FROM #tmpB;

Sandeidsbuen
Starting Member

6 Posts

Posted - 2012-09-21 : 15:36:19

Thanks for comments and help.

I will have to continue tomorrow... too late now. leaving the office :-)

I'll let you know if I can solve it with your help.

Best Regards,

Sandeidsbuen

Sandeidsbuen
Starting Member

6 Posts

Posted - 2012-09-21 : 15:42:47

By the way; when I type the following it also show the same result:

SELECT

'TAB2-line_01' = TABLE2.line_01,
'TAB1-line_01' = TABLE1.line_01,
'TAB2-line_04' = TABLE2.line_04,
'TAB1-line_04' = TABLE1.line_04,
'TAB2-line_05' = TABLE2.line_05,
'TAB1-line_05' = TABLE1.line_05

FROM TS_NAMEPLATE TABLE2

JOIN TS_NAMEPLATE_MAIN TABLE1 ON TABLE1.line_01 = TABLE2.line_01

WHERE UPPER(TABLE1.complete) = 'YES' AND TABLE2.line_05 = ''

AND TABLE2.line_01 IN ('43 LSHH-55655')

Best Regards,

Sandeidsbuen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-21 : 16:04:22

quote:
Originally posted by Sandeidsbuen

By the way; when I type the following it also show the same result:

SELECT

'TAB2-line_01' = TABLE2.line_01,
'TAB1-line_01' = TABLE1.line_01,
'TAB2-line_04' = TABLE2.line_04,
'TAB1-line_04' = TABLE1.line_04,
'TAB2-line_05' = TABLE2.line_05,
'TAB1-line_05' = TABLE1.line_05

FROM TS_NAMEPLATE TABLE2

JOIN TS_NAMEPLATE_MAIN TABLE1 ON TABLE1.line_01 = TABLE2.line_01

WHERE UPPER(TABLE1.complete) = 'YES' AND TABLE2.line_05 = ''

AND TABLE2.line_01 IN ('43 LSHH-55655')

Best Regards,

Sandeidsbuen

you mean shows what result?

rows with NULL and '' values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sandeidsbuen
Starting Member

6 Posts

Posted - 2012-09-23 : 06:50:31

I am so sorry!

I have been fooled by the data in line_04; '35 15525'. It was not the difference between NULL and '' the SQL was listing.

line_04 looked the same but it seems that there were different amount of empty spaces in the beginning of the text.
So by using the following WHERE criteria the SQL show no result for when line_01 = '43 LSHH-55655', and that's what I wanted:

WHERE UPPER(TABLE1.complete) = 'YES' AND (LTRIM(ISNULL(TABLE2.line_04,'')) <> LTRIM(ISNULL(TABLE1.line_04,'')) OR
LTRIM(ISNULL(TABLE2.line_05,'')) <> LTRIM(ISNULL(TABLE1.line_05,'')))

Thanks for all your help!
When completing my SQL I will now also add RTRIM in case there are empty spaces to the right in other rows.

Best Regards,

Sandeidsbuen

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-23 : 08:10:35

Whew! Glad you got it figured out :)

Sandeidsbuen
Starting Member

6 Posts

Posted - 2012-09-23 : 09:51:03

Yes, thanks :-)

Best Regards,

Sandeidsbuen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources