Testing using SQL and common table expressions

Sometimes I forget how to do simple things, like splitting a string into sections based on a delimiter.

Here’s a nice way to test your code on a range of input values, and see the interim results, in one hit:

with 
myobject as (
   -- put in a range of test values:
   select 'dbo.MyFunction' as NAME
   union
   select 'setup.ins_Contact'
   union
   select 'MyOtherFunction'
),
more2 as (
   -- calculate interesting attributes:
   select
      NAME,
      len( NAME )            as LENGTH_,
      charindex( '.', NAME ) as POSITION
   from myobject
)
-- perform the test:
select 
   NAME, POSITION, LENGTH_,
   case when POSITION=0 then 'dbo'
                        else substring( NAME, 1, POSITION-1 ) 
                        end as SCHEMA_,
   substring( NAME, POSITION+1 , (LENGTH_ - POSITION ) ) as OBJECT_
from more2;
;