Why does this CASE not do as expected?-CodePudding

This query fails to do as intended. Here's my example code:

create table #test (last_name varchar(20), plan_name varchar(60))
insert #test values ('Alanis', 'Alanis UT Standard Bulk Cut (DRP)')

select last_name
    , plan_name
    , case when plan_name like ''''   last_name   '%''' then SUBSTRING(plan_name, len(ltrim(rtrim(last_name))) 1, len(ltrim(rtrim(last_name))) 25) else isnull(plan_name, 'NA') end
from #test

Now what I'm expecting this to do is if the plan_name starts with the last_name, lop off the front part so I'm left with the text of the plan_name minus that prefixed last_name. But this doesn't work for some reason. It just spits out the plan_name without any change at all.

If I code more explicitly, it works just fine:

select last_name
    , plan_name
    , case when plan_name like 'alanis%' then SUBSTRING(plan_name, len(ltrim(rtrim(last_name))) 1, len(ltrim(rtrim(last_name))) 25) else isnull(plan_name, 'NA') end
from #test

I've even attempted using a QUOTENAME() function instead of my manual concatenation and I get the same result. I verified that my source table's column is in fact a varchar data type (for both the last_name and price_plan). I'm assuming I'm missing some detail, but I don't know what it is.

CodePudding user response：

The last_name is within the single quotes, remove those extra quotes and this will work.

select last_name
    , plan_name
    , case when plan_name like ''   last_name   '%' then SUBSTRING(plan_name
        , len(ltrim(rtrim(last_name))) 1, len(ltrim(rtrim(last_name))) 25) else isnull(plan_name, 'NA') end
from #test

Another suggestion is to use sql replace function instead of substring

select last_name
    , plan_name
    , case when plan_name like ''   last_name   '%' then replace(plan_name, last_name, '') else isnull(plan_name, 'NA') end
from #test