Y'herd thisun? 

“Millions of Chinese peasants died of famine during the Mao Zedong Great Leap Forward period as agricultural crops proved stubbornly unresponsive to demonstrations of mass enthusiasm. Chinese industrial production also declined as factory machinery proved similarly unresponsive. What worked was Deng Xiaoping's restoration of material incentives for good management and disciplined work”

from Reopening the Space Frontier by John Hickman

Persisted Coalescence

TaggedCoding, Database

I knew PERSISTED.  I knew COALESCE.  Dang though, I hadn't thought to put them together!

ALTER TABLE myPersonTable
    ADD FullName AS  (
REPLACE(RTrim(Coalesce(Title + ' ','') 
+ Coalesce(FirstName + ' ','') 
+ Coalesce(MiddleName + ' ', '')
+ Coalesce(LastName + ' ', '')
+ Coalesce(Suffix, '')
+ Coalesce(NULL, '')), '  ', ' ') ) PERSISTED

Wow, that just shaved a load of pasta out of a certain new totally awesome ad-hoc system ;-).  Thanks to this for that.


hey!  this is funny.  someone was over my house the other day and we were in my office talking an option then all of a sudden they switched gears and stared telling me about how they had the best rehab experience ever.  it was out of the blue and i didn't get it, but i tried to roll and let them tell me all about it because they seemed to think it was some commonality.  after they left i went back in my office and was kinda scratching my head.  then i noticed that the book on my desk was my dog-eared Seemann's Dependency Injection.  So LA.

UPDATE 2013.12.28: Even better, put the commas on the front ends and use STUFF to trim the results:

replace(rtrim( STUFF(coalesce(', ' + nullif([Street1],''),'')+coalesce(', ' + nullif([Street2],''),'')+coalesce(', ' + nullif([Street3],''),'')+coalesce(', ' + nullif([FloorSuite],''),'')+coalesce(NULL,' '),1, 2, '')),'  ',' ') PERSISTED,

Or add in some CASEs for optional formatting, like this that adds brackets around a found IDD, parens around an areacode and a leading "x" to an extension if that column has a value:

select replace(rtrim(case when coalesce(nullif([IDD]+' ',''),'')='' then '' else ('[+'+[IDD])+'] ' end + case when coalesce(nullif([AreaPrefix]+' ',''),'')='' then '' else ('('+[AreaPrefix])+') ' end +coalesce(nullif([PhoneNumber]+' ',''),'')+case when coalesce(nullif([Extension],''),'')='' then '' else ('x'+[Extension])+' ' end + coalesce(NULL,' ')), '   ',' ') from tblPhones

Boyo, the hits just keep on coming :D

home     who is smith    contact smith     rss feed π
Since 1997 a place for my stuff, and it if helps you too then all the better