REPLACE with NULL

Hello,
When I use the following:
REPLACE(CategoryType_ID, 'Non Selected', null)
all the values are replaced with NULL even when the value is not "'Non
Selected".
Any help with understanding why this happens would be appreciated.
Thanks, sck10The REPLACE function returns NULL if any one of the arguments is NULL. The
right function to do what you need is NULLIF:
NULLIF(CategoryType_ID, 'Non Selected')
You can also accomplish the same with CASE:
CASE WHEN CategoryType_ID = 'Non Selected'
THEN NULL
ELSE CategoryType_ID
END
I am assuming here the CategoryType_ID column is a character data type.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hi
"sck10" wrote:
> Hello,
> When I use the following:
> REPLACE(CategoryType_ID, 'Non Selected', null)
> all the values are replaced with NULL even when the value is not "'Non
> Selected".
> Any help with understanding why this happens would be appreciated.
> Thanks, sck10
If this is an update you can
UPDATE mYtable
SET CategoryType_ID = NULL
WHERE CategoryType_ID = 'Non Selected'
John|||Thanks Plamen Ratchev,
just what I was looking for...
sck10
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:uWahjljfHHA.4980@.TK2MSFTNGP02.phx.gbl...
> The REPLACE function returns NULL if any one of the arguments is NULL. The
> right function to do what you need is NULLIF:
> NULLIF(CategoryType_ID, 'Non Selected')
> You can also accomplish the same with CASE:
> CASE WHEN CategoryType_ID = 'Non Selected'
> THEN NULL
> ELSE CategoryType_ID
> END
> I am assuming here the CategoryType_ID column is a character data type.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>|||Thanks John,
sck10
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:EAA2A779-0D99-4F93-A464-F855323CB61A@.microsoft.com...
> Hi
> "sck10" wrote:
>> Hello,
>> When I use the following:
>> REPLACE(CategoryType_ID, 'Non Selected', null)
>> all the values are replaced with NULL even when the value is not "'Non
>> Selected".
>> Any help with understanding why this happens would be appreciated.
>> Thanks, sck10
> If this is an update you can
> UPDATE mYtable
> SET CategoryType_ID = NULL
> WHERE CategoryType_ID = 'Non Selected'
> John
>sql