sknox (7/12/2010)
webrunner (7/12/2010)
I guess my question is why does the REPLACE function replace anything if the string pattern (second parameter) isn't in the string expression (first parameter)?It doesn't. It checks for NULL before doing any replacing. This is because of the special quality of NULL. As posted before, unknown in -> unknown out.
The problem comes because we are used to thinking of replace as two operations:
1. Look for a search string within the given string.
2. If found, replace with replacement string.
The conditional there allows us to ignore an unknown or invalid third parameter if there's no replacement to be made.
But that's procedural thinking. Declarative thinking would phrase it more like* "The given string with replacement string in place of all instances of search string".
There's no conditional, so all inputs have to be considered in the function. Consequently, since one of the inputs is unknown, the result should be unknown as well.
* or some such; I'm certainly not an expert on declarative syntax.
Thanks. I think this code illustrates your explanation, if I understand correctly, that if any parameter passed in is NULL, NULL has to be returned for all rows because unknown in -> unknown out.
-- Create a temp table to store the values.
CREATE TABLE #NullTest (pk int, val varchar(15))
-- Insert the values, including one row with NULL.
INSERT INTO #NullTest VALUES (1, 'aaa');
INSERT INTO #NullTest VALUES (2, NULL);
INSERT INTO #NullTest VALUES (3, 'bbb');
INSERT INTO #NullTest VALUES (4, 'ccc');
-- SELECT 1
SELECT pk, val FROM #NullTest
-- SELECT 2
SELECT pk, REPLACE(val, 'zzz', 'ddd')
FROM #NullTest
-- SELECT 3
SELECT pk, REPLACE(val, 'zzz', NULL)
FROM #NullTest
-- SELECT 4
SELECT pk, REPLACE(val, 'zzz', NULL)
FROM #NullTest
WHERE val IS NOT NULL
-- Drop the temp table.
DROP TABLE #NullTest
In fact, SELECTs 3 and 4 return all NULLs even if there are no rows with a NULL value for the val column. Change NULL to 'eee' in the INSERT statement for pk = 2, for example.
What's funny is that even if you filter out the NULL values (as in SELECT 4 above), you still get all NULLs, except in the example above you get 3 NULL rows instead of 4.
Seems like overkill, but honestly I don't understand it 100% so I can't say that doing it any other way would be better.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html