Y'herd thisun? 

“According to studies and data from NASA, the US Department of Commerce, and The Enterprise Institute, Inc., the U.S. no longer dominates the space transportation market. In 1974, the United States enjoyed a 100% market share, In 1995, this had shrunk to 30%. [fyi: the Space Foundation reports that in 2009 the US had dropped to 18%]”

from Halfway to Anywhere by G. Harry Stine

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