NULL Is Not 'NULL'

Today someone posted a question on the Tek-Tips Forums web site
The queston was how not to insert rows with a NULL value in a certain column
The answer is of course INSERT INTO Table2 SELECT * FROM table WHERE column IS NOT NULL
This person replied that rows where the column is null are still being inserted
I turned out that there was data that had NULL character values 'NULL'
When you run SELECT NULL,'NULL' in Query Analyzer or SQL Server Managment Studio this looks identical so it's very easy to think that there is something else going on

Run the code below in Query Analyzer to understand what I mean

CREATE TABLE #test (
SomeField VARCHAR(50))

INSERT INTO #test
VALUES (NULL)

INSERT INTO #test
VALUES ('NULL')

SELECT *
FROM #test

SELECT *
FROM #test
WHERE SomeField IS NOT NULL

SELECT *
FROM #test
WHERE SomeField = 'NULL'

DROP TABLE #test