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;
;