smithvoice.com
 Y'herd thisun? 

“You can't make fun of something until you've done it... here is exactly and fully (and easily) how to really do ASP.Net on linux”

from this tutorial by smith

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 smithvoice.com