jwcolby
jwcolby at colbyconsulting.com
Fri Jun 6 09:43:05 CDT 2008
I found a database property / options / Concat null yields null, which it CLAIMS will change this behavior. However it still yields a null regardless of whether I set this to true or false. Ths ISNULL() thing no doubt works but I have to concat 7 different fields for 84 million records. I suspect that I would be setting myself up for a week long query. Given that the property makes no difference I may be forced to do this however. John W. Colby www.ColbyConsulting.com Paul Hartland wrote: > Off the top of my head try: > > Select ISNULL(HouseNumberPrefix,'') + ' ' + ISNULL(HouseNumber,'') + ' ' + > ISNULL(HouseNumberSuffix,'') + ' ' + ISNULL(Direction,'') + ' ' + > ISNULL(StreetName,'') + ' ' + ISNULL(Mode,'') + ' ' + ISNULL(Quadrant,'') + > ' ' + ISNULL(Appt#,'') > > Paul Hartland > > 2008/6/6 jwcolby <jwcolby at colbyconsulting.com>: > >> I have a database that has split the address line into >> >> HouseNumberPrefix >> HouseNumber >> HouseNumberSuffix >> Direction >> StreetName >> Mode (N, NW etc) >> Quadrant >> Appt# >> >> I need to put Humpty back together again to feed off to >> Address Validation. How would I do that in SQL? >> >> I THINK I can just append them all together with spaces >> between the parts and that would be fine EXCEPT that when >> you do something like NULL + SomeString you end up with null. >> >> How would I do what I am trying to do in SQL? >> >> -- >> John W. Colby >> www.ColbyConsulting.com <http://www.colbyconsulting.com/> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> > > >