SQL Server : ISNULL(compound NULL condition, 'a string') returns only the 1st character, under certain circumstance(s) -

i'm self-taught, vaguely competent sql user. view i'm writing, i'm trying develop 'conditional left' string-splitting command (presumably later joined 'conditional right' - whereby:

• if string (let's call 'haystack') contains particular pattern (let's call 'needle'), it pruned left of pattern
• otherwise, entire string passed unaltered.

so, if our pattern ' - ',

• 'a long string - containing pattern' output 'a long string'
• 'a string without pattern' returned as-is.

rather using crudest ways this, i'm trying come way avoids having repeat clause (such if 0 < charindex, take charindex - 1, etc.) , instead leverages conditional nulling.

yet - here's trying creative - i've hit seems basic stumbling block. please observe following code , results, , let me know whether can replicate - , hence whether it's bug or i've missed peculiar. have tested on sql server both 2008 r2 , 2014, both express editions.

select     -- isnull: returns 'a big old string'     isnull(null, 'a big old string'),      -- nullif: returns null     left(         'a big old string',         nullif         (             charindex             (                 'needle',                 'haystack'             ), 0         ) - 1     ),      -- combined: returns 'a' (1st character of isnull condition)     isnull(         left         (             'a big old string', -- input string. in reality, column alias, etc.             nullif             (                 charindex       -- search splitting pattern                 (                     'needle',                     'haystack'                 ), 0            -- if it's not found, return null instead of usual 0             ) - 1               -- subtraction produces null, not invalid negative index         ),         'a big old string'      -- if pattern not found, should return input unaltered     );  /* ---------------- ---- ---- big old string null  (1 row(s) affected) */ 

why these 2 clauses work expected in isolation, when combine them, rather getting sum of effects, 1st character of isnull string - 'a'?

is there kind of implicit cast varchar(1)? deliberately casting varchar(max) made no difference. else going on here?

am doing stupid? because here, can't figure out i'm doing wrong, , seems bug. hoped testing on 2014 prove bug in old 2008 r2, alas, act identically (or, rather, don't).

thanks in advance for, hopefully, saving me presumably evening of baffled existential crisis.

there 2 parts problem, first nature of isnull operator, use datatype , length of first argument. simple example be:

declare @a char(1) = null,         @b varchar(max) =  'this test';  select top 1 test = isnull(@a, @b); 

this returns t , checking execution plan xml can see implicit conversion of "this test" char(1):

<scalaroperator scalarstring="isnull([@a],convert_implicit(char(1),[@b],0))">     <intrinsic functionname="isnull">     <scalaroperator>         <identifier>         <columnreference column="@a" />         </identifier>     </scalaroperator>     <scalaroperator>         <convert datatype="char" length="1" style="0" implicit="true">         <scalaroperator>             <identifier>             <columnreference column="@b" />             </identifier>         </scalaroperator>         </convert>     </scalaroperator>     </intrinsic> </scalaroperator> 

your example not quite straightforward since don't have types nicely defined above, if define dataypes:

declare @a varchar(max) =  'a big old string',         @b varchar(max) = 'needle',         @c varchar(max) = 'haystack';  select top 1 isnull(left(@a, nullif(charindex(@b, @c), 0) - 1), @a); 

we result expected. else happening under hood. query plan not delve inner workings of constant evaluation, following demonstrates happening:

select  test = left('a big old string', nullif(charindex('needle', 'haystack'), 0) - 1)    #t;  select  t.name, c.max_length    tempdb.sys.columns c         inner join sys.types t             on t.system_type_id = c.system_type_id             , t.user_type_id = c.user_type_id   [object_id] = object_id(n'tempdb..#t');  ---------------- name        max_length varchar     1 

basically, using select into sytax left expression shows when null length passed left resulting datatype varchar(1), however, not case. if hard code null left function:

select  test = left('a big old string', null)    #t;  -------------------- name        max_length varchar     16 

then legnth of string passed, case statement should optimised away same thing, yields length of 1 again:

select  top 1 test = left('a big old string', case when 1 = 1 null else 1 end)    #t;  ---------------- name        max_length varchar     1 

i suspect related default behaviour of varchar, default length 1, e.g:

declare @a varchar = 'this test';  select  value = @a,                                         -- t         maxlength = sql_variant_property(@a, 'maxlength')   -- 1 

but can't tell why see different behaviour null , case when 1 = 1 null else 1 end. if wanted bottom of going on in constant evaluation think need re-ask on dba site , hope 1 of real sql server gurus picks up.

in summary, left(<constant>, <constant expression>) <constant expression> yields null implicitly typed varchar(1), , implicit type used in isnull evaluation.

for worth, if explicitly type result of left function expected result:

select isnull(             cast(                 left(                     'a big old string',                      nullif(charindex('needle', 'haystack'), 0) - 1                     )                  varchar(max))                 , 'a big old string'); 

---

an additional point when don't want repeat expressions (if 0 < charindex, take charindex - 1, etc.), there 2 things should know, first nullif(<expression>, <value>) expands case statement - case when <expression> = <value> null else <expression> end, repeated, second doesn't matter, sql server can identify same expression used twice, , evaluate once , refer same result each time used.