smithvoice.com
 Y'herd thisun? 

“Now with NuGet!!!!... just grab a library because developers are too darned stupid to get into code. I dunno WROX, this attitude (and the overwhelming MVC3 EF-ONLY blackbox approach that the book and MS are more-than-gently pushing) brings back the days of forms plastered with MicroHelp VBXes. Ah well, at least we all glue the libraries together with semicolons now ;-).”

from this "Professional Book" review 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